Django - How to combine select_related and prefetch_related to reduce the number of queries

Question

I would like to generate a json which is the aggregation of data from several tables linked together (OneToOne and ManyToMany relationships).

models.py

Team(models.Model):
    staff = models.OneToOneField(Staff, on_delete=models.CASCADE, related_name='team_staff')
    function = models.CharField()

Staff(models.Model):
    firstname = models.CharField()
    lastname = models.CharField()
    courses = models.ManyToManyField(Course, related_name='staff_course')

Course(models.models):
    name = models.CharField()
    begin= models.DateField()
    end = models.DateField()

    def __str__(self):
        return '%s - %s' % (self.name, self.begin, self.end)

views.py

def all_team(request):
    team_query = Team.objects.all().select_related('staff')
    team_list = []
        for person in team_query:
            courses = Staff.objects.get(id=person.staff.id).courses.order_by('-begin')
            team_list.append({'fields': {'id': obj.id,
                                         'function': person.function,
                                         'firstname': person.staff.firstname,
                                         'lastname': person.staff.lastname,
                                         'last_course': str(courses.first()),
                                        }})
        json_data = json.dumps(team_list, cls=DjangoJSONEncoder)
        return HttpResponse(json_data, content_type='application/json')

As you can see, this is not very efficient. For each member of the team, you make a query to get the last course taken. Can we add something like :

staff_query = Staff.objects.all().prefetch_related(Prefetch('courses', queryset=Course.objects.only('name').all()))

and merge / combine it with the team_query.

Thank you in advance for your advice

It's currently in the project stage. I'm using an SQLite file. When this is deployed I'm thinking of using a Postgres server. — P. Bourzeix
– P. Bourzeix, Commented May 25, 2020 at 12:34

D Malan · Accepted Answer · 2020-06-13 19:20:58Z

3

You could use the normal join syntax to prefetch a related field from a related field:

team_query = (
  Team.objects.all()
  .select_related('staff')
  .prefetch_related(
    Prefetch('staff__courses').order_by('-begin'))
  )
)

Then getting the last course would become:

'last_course': str(person.staff.courses.first())

edited Jun 13, 2020 at 19:20

answered Jun 5, 2020 at 14:16

D Malan

11.6k3 gold badges35 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django - How to combine select_related and prefetch_related to reduce the number of queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related