[Django]-Retrieve details from multiple tables using django query

by

3👍

✅

If you are using Django Rest Framework, then it is easy to define the computed values in serializer:

No need to make a query in SQL

Just follow the steps:

  • Create Serializer of Job_Report model.
class Job_ReportSerializer(serializers.ModelSerializer):
   job = serializers.SerializerMethodField()
   sol = serializers.SerializerMethodField()
   dept = serializers.SerializerMethodField()

  class Meta:
      model = Job_Report
      fields = '__all__'

  def get_job(self, obj): 
      # 'get_' + 'attribute-name' to give definition
      return obj.job.job_name

   def get_sol(self, obj):
       return obj.job.folder.sol.sol_name

   . 
   .
   .
   # Define for all SerializerMethodField

Follow this for more info about DRF (Django REST Framework) https://www.django-rest-framework.org/tutorial/quickstart/
You’ll take hardly 2 hours to complete the tutorial, but trust me it’ll save your days of work.

Let me know if you want a solution without DRF.

👤Charanjit Singh

3👍

models.py

All your models as you defined, 

Just make one change:
- Do not use ids. Just use foreign keys.

class Job_Report(models.Model):
    job: models.ForeignKey(Job, models.DO_NOTHING)
    status:models.CharField(max_length=7)
    date: models.DateField()
    time: models.TimeField()    
    duration = models.BigIntegerField(blank=True, null=True)

class Job(models.Model):
    name:models.CharField(max_length=500)
    folder:models.ForeignKey(Job_folder, models.DO_NOTHING)

class Job_folder(models.Model):
     name:models.CharField()
     repo:models.ForeignKey(Sol_folder, models.DO_NOTHING)

class Sol_folder(models.Model):
      name:models.CharField(max_length=500)
      sol:models.ForeignKey(Solution, models.DO_NOTHING)

class Solution(models.Model):
      name:models.CharField(max_length=500)
      dept:models.ForeignKey(Department, models.DO_NOTHING)

class Department(models.Model):
      name:models.CharField(max_length=500)

Serializers.py


class Job_ReportSerializer(serializers.ModelSerializer):
   job = serializers.SerializerMethodField()
   sol = serializers.SerializerMethodField()
   dept = serializers.SerializerMethodField()

  class Meta:
      model = Job_Report
      fields = '__all__'

  def get_job(self, obj): 
      # 'get_' + 'attribute-name' to give definition
      return obj.job.name

   def get_sol(self, obj):
       return obj.job.folder.sol.name

   . 
   .
   .
   # Define for all SerializerMethodField

views.py


@api_view(['GET'])
def list_reports(request):
   job_reports = Job_Report.objects.all()
   data = JobReportSerializer(job_reports, many=True, context={'request':request}).data
   return Response(data)

@api_view(['GET'])
def detail_report(request, pk):
   job_report = Job_Report.objects.get(id=pk)
   data = JobReportSerializer(job_report,context={'request':request}).data
   return Response(data)

urls.py

  • Add Urls to the functional views

Boom!!! API is ready, don’t forget to add ‘rest_framework’ in INSTALLED_APPS in settings.py

👤Charanjit Singh

Leave a comment