sqldjangodjango-rest-frameworkdjango-mptt

Why are all my SQL queries being duplicated 4 times for Django using "Prefetch_related" for nested MPTT children?


I have a Child MPTT model that has a ForeignKey to itself:

class Child(MPTTModel):
    title = models.CharField(max_length=255)
    parent = TreeForeignKey(
        "self", on_delete=models.CASCADE, null=True, blank=True, related_name="children"
    )

I have a recursive Serializer as I want to show all levels of children for any given Child:

class ChildrenSerializer(serializers.HyperlinkedModelSerializer):
    url = HyperlinkedIdentityField(
        view_name="app:children-detail", lookup_field="pk"
    )

    class Meta:
        model = Child
        fields = ("url", "title", "children")

    def get_fields(self):
        fields = super(ChildrenSerializer, self).get_fields()
        fields["children"] = ChildrenSerializer(many=True)
        return fields

I am trying to reduce the number of duplicate/similar queries made when accessing a Child's DetailView.

The view below works for a depth of 2 - however, the "depth" is not always known or static.

class ChildrenDetailView(generics.RetrieveUpdateDestroyAPIView):
    queryset = Child.objects.prefetch_related(
        "children",
        "children__children",
        # A depth of 3 will additionally require "children__children__children",
        # A depth of 4 will additionally require "children__children__children__children",
        # etc.
    )
    serializer_class = ChildrenSerializer
    lookup_field = "pk"

Note: If I don't use prefetch_related and simply set the queryset as Child.objects.all(), every SQL query is duplicated four times... which I have no idea why.

How do I leverage a Child's depth (i.e. the Child's MPTT level field) to optimize prefetching? Should I be overwriting the view's get_object and/or retrieve?

Does it even matter if I add a ridiculous number of depths to the prefetch? E.g. children__children__children__children__children__children__children__children? It doesn't seem to increase the number of queries for Children objects that don't require that level of depth.

Edit:

Hm, not sure why but when I try to serialize any Child's top parent (i.e. MPTT's get_root), it duplicates the SQL query four times???

class Child(MPTTModel):
    ...
    @property
    def top_parent(self):
        return self.get_root()
    

class ChildrenSerializer(serializers.HyperlinkedModelSerializer):
    ...
    top_parent = ParentSerializer()
    fields = ("url", "title", "children", "top_parent")

Edit 2

Adding an arbitrary SerializerMethodField confirms it's being queried four times... for some reason? e.g.

class ChildrenSerializer(serializers.HyperlinkedModelSerializer):
    ...
    foo = serializers.SerializerMethodField()

    def get_foo(self, obj):
        print("bar")
        return obj.get_root().title

This will print "bar" four times. The SQL query is also repeated four times according to django-debug-toolbar:

SELECT ••• FROM "app_child" WHERE ("app_child"."parent_id" IS NULL AND "app_child"."tree_id" = '7') LIMIT 21

4 similar queries. Duplicated 4 times.


Solution

  • Are you using DRF's browsable API? It initializes serializer 3 more times for HTML forms, in rest_framework.renderers.BrowsableAPIRenderer.get_context.

    If you do the same request with, say, Postman, "bar" should get printed only once.