Django: DB queries count optimization

Question

I have models (one user can has multiple employees):

from django.db import models


class User(models.Model):
    username = ...
    first_name = ...
    last_name = ...


class OrgUnit(models.Model):
    name = ....
    address = ...


class Employee(models.Model):
    personnel_no = ...
    user = models.ForeignKey(User, related_name='employees'...)
    orgunit = models.ForeignKey(OrgUnit, ...)

Serializers and views:

class EmployeeSerializer(serializers.ModelSerializer):
    orgunit = serializers.CharField(source='orgunit.name') <==== one orgunit - one query to DB

    class Meta:
        model = Employee
        fields = '__all__'


class CustomUserSerializer(serializers.ModelSerializer):
    employees = EmployeeSerializer(many=True)

    class Meta:
        model = User
        fields = '__all__'


class UsersViewSet(ViewSet):
   
    def me(self, request):
        serializer = CustomUserSerializer(request.user)
        return Response(serializer.data)

When serializing orgunit.name per one orgunit one query to DB is being performed. How to avoid this? How to prefetch employees related_name and its orgunits?

Can you explain your questions properly? Like do you want to get all the related data (of foreign keys) when you call objects from OrgUnit or Employee model? — SimbaOG
– SimbaOG, Commented Feb 16, 2022 at 16:15

Brian Destura · Accepted Answer · 2022-02-16 23:33:10Z

2

In this case you can use Prefetch..[Django-doc] and prefetch_related_objects..[Django-doc] to fetch all related employees with orgunit selected as well:

from django.db.models import Prefetch, prefetch_related_objects


class UsersViewSet(ViewSet):
    def me(self, request):
        prefetch_related_objects(
            [request.user], 
            Prefetch(
                "employees", 
                queryset=Employee.objects.select_related("orgunit"),
            ),
        )

        serializer = CustomUserSerializer(request.user)
        return Response(serializer.data)

answered Feb 16, 2022 at 23:33

Brian Destura

12.1k3 gold badges22 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

Django: DB queries count optimization

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