Django prefetch related used inside a serializer method making too many queries. How to use prefetch related to reduce queries in following case?

Question

By investigating why some apis are taking too much time to respond I encountered that it's because of the db queries increases as the queryset increases. In Django select related and prefetch related will helps to reduce this problem. Here I am trying to solve such a problem where I used prefetch related in a serializer and still making too much db queries.

models.py

class Order(models.Model):
    orderid = models.AutoField(primary_key=True)
    order_number = models.CharField(max_length=20, unique=True)
    #....

class SubOrder(models.Model):
    suborderid = models.AutoField(primary_key=True)
    orderid = models.ForeignKey(
        Order, on_delete=models.CASCADE, related_name="orderitems", db_column="orderid"
    )
    order_number = models.CharField(max_length=50)
    product_quantity = models.PositiveIntegerField()
    #....

views.py

class GetOrderDetailsByStore(mixins.ListModelMixin, generics.GenericAPIView):

  
    def get(self, request, order_number=None, *args, **kwargs):

        start = time.time()
        logger.info("started %s", start)
        store_id = self.kwargs["storeid"]
        status_list = [SubOrder.Suborder_Status.PAYMENT_INITIATED,
                       SubOrder.Suborder_Status.PAYMENT_FAILED,
                       SubOrder.Suborder_Status.PAYMENT_PENDING,
                       SubOrder.Suborder_Status.ORDER_INITIATED]
        
        order_items = Order.objects.filter(Q(storeid=store_id) &~Q(order_status__in=status_list)).order_by(
                "-created_date"
            )
        order_items_eager_loading = AllOrderSellerSerializer.setup_eager_loading(
                order_items)
        serializer = AllOrderSellerSerializer(order_items_eager_loading, many=True).data

        end = time.time()
        diff = end - start
        logger.info("ended %s", end)
        logger.info("time taken %s", diff)
        return Response({"message": "success", "data": serializer}, status=status.HTTP_200_OK)

serializer.py

class AllOrderSellerSerializer(serializers.ModelSerializer):

    orderitems = AllOrderItemsSellerSerializer(many=True)
    store_reference = serializers.CharField(source="store_reference.store_reference")
    user_reference = serializers.CharField(source="user_reference.user_reference")
    number_of_items = serializers.SerializerMethodField("get_number_of_items")

    class Meta:
        model = Order
        fields = ["order_number", "order_request_number", "store_reference",
                  "user_reference","number_of_items", "total_amount", "orderitems"]

    @staticmethod
    def get_number_of_items(obj):
        list_of_pdt_quantity_of_order = [order_item.product_quantity for order_item in obj.orderitems.filter(orderid=obj.orderid)]
        total_ordered_products_quantity = sum(list_of_pdt_quantity_of_order)
        return total_ordered_products_quantity
        # return obj.orderitems.count()


    @staticmethod
    def setup_eager_loading(queryset):
        """ Perform necessary eager loading of data. """
        queryset = queryset.prefetch_related('orderitems')
        queryset = queryset.select_related('user_reference', 'store_reference')
        return queryset

Here the number_of_items field takes the sum of prodcut_quantity. But here the db queries get increased as the orders increases. if this "number_of_items" removed this takes only 2 queries for any number of orders, but because of this field queries get increasing and api taking much time to respond. Does this using prefetch related in a wrong way or is there any way to solve this much of db queries?

Answer 1

prefetch_related will not reduce the number of SQL queries, this behaviour is documented:

select_related works by creating an SQL join and including the fields of the related object in the SELECT statement. [...]

prefetch_related, on the other hand, does a separate lookup for each relationship, and does the ‘joining’ in Python. This allows it to prefetch many-to-many and many-to-one objects, which cannot be done using select_related, in addition to the foreign key and one-to-one relationships that are supported by select_related.