How can I reduce recursive MPTT tree queries when serializing with DRF

Question

Working on an e-commerce project. Project has related models. Category model has MPTT inheritance. It using Django Rest Framework for communicate between API's. A foreign service recently wants me to put full Category path into XML response on my side. But this request caused very high db queries. I need to reduce queries but I can't figure out how to do this within DRF serialization. I tried a few way. My final approach is below with model view and serializations.

class Category(MPTTModel):
    parent = TreeForeignKey('self', blank=True, null=True, related_name='children')
    root = TreeForeignKey('self', blank=True, null=True, related_name='leaf')
    name = models.CharField(max_length=100)

class ProductMeta(models.Model):
    ...
    category = models.ForeignKey('Category', null=True, blank=True, db_index=True, related_name='category')
    ...

class Product(models.Model):
    ...
    meta = models.ForeignKey(ProductMeta, related_name='product')
    ...

And some DRF viewsets are renders model data to XML

class ProductMetaBaseViewSet(viewsets.ModelViewSet):
    def get_serializer_class(self):
        return ProductMetaSerializer

    def get_queryset(self):
        queryset = ProductMeta.objects.all().prefetch_related('products', 'category__root')
        return self.paginate_queryset(queryset)

    def list(self, request):
        serializer = ProductMetaSerializer(self.get_queryset(), many=True)
        return Response(serializer.data)


class ProductMetaXMLViewSet(ProductMetaBaseViewSet, viewsets.ModelViewSet):
    parser_classes = (XMLParser,)
    renderer_classes = (XMLRenderer,)

And here is the serializers to get data:

class RootCategorySerializer(serializers.ModelSerializer):
    class Meta:
        model = Category
        fields = ('id', 'name')


class CategorySerializer(serializers.ModelSerializer):
    root = RootCategorySerializer()
    full_category_path = serializers.SerializerMethodField()

    class Meta:
        model = Category
        fields = ('name', 'root', 'category_path')

    def get_full_category_path(self, obj):
        related_ancestor_name_list = []
        related_ancestor_list = []

        next_rel_name = ""
        next_rel = None
        cat_level = obj.get_level()
        for i in range(cat_level):
            if i <= 0 and not next_rel_name:
                next_rel_name = 'name'
                next_rel = "parent"
            else:
                next_rel_name = "{}__{}".format("parent", next_rel_name)
                next_rel = "{}__parent".format(next_rel)

            related_ancestor_name_list.append(next_rel_name)
            if next_rel is not None:
                related_ancestor_list.append(next_rel)

        print(related_ancestor_name_list, related_ancestor_list)
        cobj = Category.objects.filter(pk=obj.pk).select_related(*related_ancestor_list).prefetch_related(*related_ancestor_list).values_list(*related_ancestor_name_list[::-1]).first()
        return ' > '.join(cobj)


class ProductSerializer(serializers.ModelSerializer):

    class Meta:
        model = Product
        fields = ('price', 'stock')


class ProductMetaSerializer(serializers.ModelSerializer):
    products = ProductSerializer(many=True, read_only=True)
    category = CategorySerializer(read_only=True)

    class Meta:
        model = ProductMeta
        fields = ('name', 'category', 'products')

On my test database, if I don't use get_full_category_path method there are 20 ish queries logged into logger. When I need to use that method for fetching the full category path queries are rising to over 100.

I posted my most recent try. I also tried to use get_ancestors method of the MPTT but it's not affected to db hit count. Either way, on each product object serialization produces very high amount of Category model query.

PS: I know the best option is caching the tree, but I'm really wondering if there is a way to reduce DB hits when making recursive MPTT queries.

How can I reduce recursive MPTT tree queries when serializing with DRF

0 Answers0