[SOLVED] Speeding up Django Rest Framework Model Serializer N+1 Query problem

Speeding up Django Rest Framework Model Serializer N+1 Query problem

I have a DRF ModelSerializer class that serializes anOrder model. This serializer has a field:

num_modelA = serializers.SerializerMethodField() `

def get_num_modelA(self, o):
    r = ModelA.objects.filter(modelB__modelC__order=o).count()

    return r

Where ModelA has a ForeignKey field modelB, ModelB has a ForeignKey field modelC, and ModelC has a ForeignKey field order.

The problem with this is obviously that for each order that gets serialized it makes an additional query to the DB which slows performance down.

I've implemented a static method setup_eager_loading as described here that fixed the N+1 query problem for other fields I was having.

@staticmethod
def setup_eager_loading(queryset):
    # select_related for "to-one" relationships
    queryset = queryset.select_related('modelD','modelE')
    
    return queryset

My idea was I could use prefetch_related as well to reduce the number of queries. But I am unsure how to do this since Order and ModelA are separated by multiple foreign keys. Let me know if any other information would be useful

Solution

You can work with an annotation:

from django.db.models import Count

# …

@staticmethod
def setup_eager_loading(queryset):
    # select_related for "to-one" relationships
    return queryset.select_related('modelD','modelE').annotate(
        num_modelA=Count('modelC__modelB__modelA')
    )

in the serializer for your Order, you can then use num_modelA as an IntegerField:

from rest_framework import serializers

class OrderSerializer(serializers.ModelSerializer):
    num_modelA = serializers.IntegerField()

    class Meta:
        model = Order
        fields = ['num_modelA', 'and', 'other', 'fields']