How to apply a filter for each row of the same queryset on Django

Gustavo William 0
Viewed 30

I have the a model Product which has a relation with itself using the key "parent".

This way, to get the parent of a product i just do product.parent and if i want the children of a product i do product.product_set.all().

What i want to do is to do an annotate on a queryset of parent products and sum the stock of each product with the stock of its children. Something similar to this:

qs = Product.objects.filter(is_parent=True)
qs = qs.annotate(stock_sum=Sum(Product.objects.filter(parent_id=F('id')).values_list['stock']))

Is this possible? Will it result in many queries or django know a way of handling this with a few joins?

django-models django python
1 Answers

Save for one syntax error (square brackets rather than parentheses after values_list) this should work as you intended.

This induces only a single query. Note you can check the SQL that the ORM generates by accessing the .query property of the queryset (the following was subsequently run through a SQL pretty-printer):

>>> str(qs.query)
SELECT
    "yourapp_product"."id",
    "yourapp_product"."parent_id",
    "yourapp_product"."stock",
    SUM((SELECT U0."stock" FROM "yourapp_product" U0 WHERE U0."parent_id" = (U0."id"))) AS "stock_sum"
FROM
    "yourapp_product"
WHERE
    "yourapp_product"."is_parent"
GROUP BY
    "yourapp_product"."id",
    "yourapp_product"."parent_id",
    "yourapp_product"."stock";
Daniel W. Steinbrook0
Related