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

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?

CodePudding user response：

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";

CodePudding user response：

Not sure how to solve the problem with Django ORM.

But in SQL you can do it like this:

SELECT
    p.id,
    (
    SELECT
        SUM(c.stock)
    FROM
        products_product c
    WHERE
        c.parent_id = p.id)   p.stock as stock_sum
FROM
    products_product p
WHERE
    p.parent_id is null

You can execute a sql query in Django like this:

products = Product.objects.raw("""
SELECT
    p.id,
    (
    SELECT
        SUM(c.stock)
    FROM
        products_product c
    WHERE
        c.parent_id = p.id)   p.stock as stock_sum
FROM
    products_product p
WHERE
    p.parent_id is null
""")

and then use it like this for example [(p.id, p.stock_sum) for p in products]