Django Rest FrameWork Reduce number of queries using group by

Question

I am writing an api using Django Rest Frameworks. The api fetches a list of clients.A Clients has many projects. My api should returns the list of clients with number of projects completed, pending and total. My api works, but it has too many sql queries. The api is paginated

class ClientViewSet(ModelViewSet):
    """
     A simple view for creating clients, updating and retrieving
    """
    model = Client
    queryset = Client.objects.all()
    serializer_class = ClientSerializer

Now my client Serializer


class ClientSerializer(serializers.ModelSerializer):
    total_projects_count = serializers.SerializerMethodField()
    on_going_projects_count = serializers.SerializerMethodField()
    completed_projects_count = serializers.SerializerMethodField()

    class Meta:
        model = Client
        fields  = __all__

    def get_total_projects_count(self, obj):
        return obj.total_projects_count()

    def get_on_going_projects_count(self, obj):
        return obj.on_going_project_count()

    def get_completed_projects_count(self, obj):
        return obj.completed_projects_count()

Project has a client foreign key. I tried to fetch all products like below and group by using annotate. But annotate worked only on a single field.

projects = Project.objects.filter(client__in=queryset).values('client', 'status')

How to do group by on multiple fields and pass that extra argument to serializer. Or is there any better approach. I also tried prefetch_related but the total_projects_count was still exuecting new sql queries

Can you add the `Client` model with the three count methods? — Iain Shelvington, Feb 25 '21 at 19:12

score 1 · Answer 1 · answered Feb 25 '21 at 20:13

You need to annotate the calculated fields in the queryset and then, instead of calling the methods, use the annotated columns to return the relevant result. This will make sure that all data is retrieved using a single query, which will definitely be faster.

Update your queryset.

class ClientViewSet(ModelViewSet):
    """
     A simple view for creating clients, updating and retrieving
    """
    model = Client
    queryset = Client.objects.annotate(total_projects_count_val=...)
    serializer_class = ClientSerializer

Then, in your serializer, use the annotated column

class ClientSerializer(serializers.ModelSerializer):
    total_projects_count = serializers.SerializerMethodField()
    on_going_projects_count = serializers.SerializerMethodField()
    completed_projects_count = serializers.SerializerMethodField()

    class Meta:
        model = Client
        fields  = __all__

    def get_total_projects_count(self, obj):
        return obj.total_projects_count_val

    ...

Looking at the method names, I think you will need Case-When annotation.

score 1 · Answer 2 · answered Feb 26 '21 at 05:47

I reduced the query by using the below queries

from django.db.models import Count, Q

pending = Count('project', filter=Q(project__status="pending"))
finished = Count('project', filter=Q(project__status="finished"))
queryset = Client.objects.annotate(pending=pending).annotate(finished=finished)

Now was able to access queryset[0].finished etc . As I was using pagination provided drf the query generated was

SELECT "clients_client"."id",
       "clients_client"."created_at",
       "clients_client"."updated_at",
       "clients_client"."client_name",
       "clients_client"."phone_number",
       "clients_client"."email",
       "clients_client"."address_lane",
       "clients_client"."state",
       "clients_client"."country",
       "clients_client"."zipCode",
       "clients_client"."registration_number",
       "clients_client"."gst",
       COUNT("projects_project"."id") FILTER (WHERE "projects_project"."status" = 'pending') AS "pending",
       COUNT("projects_project"."id") FILTER (WHERE "projects_project"."status" = 'finished') AS "finished"
  FROM "clients_client"
  LEFT OUTER JOIN "projects_project"
    ON ("clients_client"."id" = "projects_project"."client_id")
 GROUP BY "clients_client"."id"
 ORDER BY "clients_client"."id" ASC
 LIMIT 6

Django Rest FrameWork Reduce number of queries using group by

2 Answers2