Django REST Serializer executing N + 1 database invokes multiple nested relationships, 3 levels

Question

I have a situation where my model has a foreign key relationship:

# models.py class Child(models.Model): parent = models.ForeignKey(Parent,) class Parent(models.Model): pass 

and my serializer:

 class ParentSerializer(serializer.ModelSerializer): child = serializers.SerializerMethodField('get_children_ordered') def get_children_ordered(self, parent): queryset = Child.objects.filter(parent=parent).select_related('parent') serialized_data = ChildSerializer(queryset, many=True, read_only=True, context=self.context) return serialized_data.data class Meta: model = Parent 

When I call Parent in my views for N number of parents, Django makes N database call numbers inside the serializer when it captures children. Is there a way to get ALL children for ALL parents to minimize the number of calls in the database?

I tried this, but it does not seem to solve my problem:

 class ParentList(generics.ListAPIView): def get_queryset(self): queryset = Parent.objects.prefetch_related('child') return queryset serializer_class = ParentSerializer permission_classes = (permissions.IsAuthenticated,) 

EDIT

I updated the code below to reflect the feedback from Alex .... which solves N + 1 for one nested relationship.

 # serializer.py class ParentSerializer(serializer.ModelSerializer): child = serializers.SerializerMethodField('get_children_ordered') def get_children_ordered(self, parent): # The all() call should hit the cache serialized_data = ChildSerializer(parent.child.all(), many=True, read_only=True, context=self.context) return serialized_data.data class Meta: model = Parent # views.py class ParentList(generics.ListAPIView): def get_queryset(self): children = Prefetch('child', queryset=Child.objects.select_related('parent')) queryset = Parent.objects.prefetch_related(children) return queryset serializer_class = ParentSerializer permission_classes = (permissions.IsAuthenticated,) 

Now let's say I have another model that is a grandson:

 # models.py class GrandChild(models.Model): parent = models.ForeignKey(Child,) class Child(models.Model): parent = models.ForeignKey(Parent,) class Parent(models.Model): pass 

If I put the following in my views.py for the queryset parent:

queryset = Parent.objects.prefetch_related(children, 'children__grandchildren')

It doesn't seem like these grandchildren are being ported to ChildSerializer, and so again I am running another N + 1 issue. Any thoughts on this?

EDIT 2

Perhaps this will provide clarity ... Maybe the reason I still encounter calls to the N + 1 database is because my children and grandchild classes are Polymorphic .... ie

 # models.py class GrandChild(PolymorphicModel): child = models.ForeignKey(Child,) class GrandSon(GrandChild): pass class GrandDaughter(GrandChild): pass class Child(PolymorphicModel): parent = models.ForeignKey(Parent,) class Son(Child): pass class Daughter(Child): pass class Parent(models.Model): pass 

and my serializers look like this:

 # serializer.py class ChildSerializer(serializer.ModelSerializer): grandchild = serializers.SerializerMethodField('get_children_ordered') def to_representation(self, value): if isinstance(value, Son): return SonSerializer(value, context=self.context).to_representation(value) if isinstance(value, Daughter): return DaughterSerializer(value, context=self.context).to_representation(value) class Meta: model = Child class ParentSerializer(serializer.ModelSerializer): child = serializers.SerializerMethodField('get_children_ordered') def get_children_ordered(self, parent): queryset = Child.objects.filter(parent=parent).select_related('parent') serialized_data = ChildSerializer(queryset, many=True, read_only=True, context=self.context) return serialized_data.data class Meta: model = Parent 

Plus for Granddaughter, grandson, I will spare you the details of the code, but I think you will get the picture.

When I run my view for ParentList and control the database queries, I get something in accordance with thousands of queries, only for a few parents.

If I run the same code in the django shell, I can execute the same request from no more than 25 requests. I suspect, maybe this is due to the fact that I'm using the django-polymorphic library? The reason is that there is a table of the Child and GrandChild database, in addition to each table "Son / Daughter", "Grandson / Granddaughter", a total of 6 tables. through these objects. So my gut tells me that I'm missing these polymorphic tables.

Or maybe there is a more elegant solution for my daata model?

Answer 1

As far as I remember, nested serializers have access to predefined relationships, just make sure you don't change the set of queries (i.e. use all() ):

 class ParentSerializer(serializer.ModelSerializer): child = serializers.SerializerMethodField('get_children_ordered') def get_children_ordered(self, parent): # The all() call should hit the cache serialized_data = ChildSerializer(parent.child.all(), many=True, read_only=True, context=self.context) return serialized_data.data class Meta: model = Parent class ParentList(generics.ListAPIView): def get_queryset(self): children = Prefetch('child', queryset=Child.objects.select_related('parent')) queryset = Parent.objects.prefetch_related(children) return queryset serializer_class = ParentSerializer permission_classes = (permissions.IsAuthenticated,) 

Answer 2

This question is a bit old, but I ran into a very similar problem and managed to drastically reduce db calls. It seems to me that Django-mptt will make it easier for you.

One way is to define one model using ForeignKey. Thus, you can recognize the hierarchy by this level in the tree. For instance:

 class Person(MPTTModel): parent = TreeForeignKey('self', null=True, blank=True, related_name='children', db_index=True) 

You can find out if the object is parent if Person.level = 0. If it is 1, it is a child, 2 grandchildren, etc ...

Then you can change your code to the following:

 # serializers.py class ChildSerializer(serializers.ModelSerializer): children = serializers.SerializerMethodField() def get_children(self, parent): queryset = parent.get_children() serialized_data = ChildSerializer(queryset, many=True, read_only=True, context=self.context) return serialized_data.data # views.py class ParentList(generics.ListAPIView): def get_queryset(self): queryset = cache_tree_children(Person.objects.all()) 

In doing so, you fix the problem with N + 1. For example, if you want to add a new ForeignKey to the genre model, you can simply change the last line:

 queryset = cache_tree_children(Person.objects.filter(channel__slug__iexact=channel_slug).select_related('genre'))