Django annotate sum with a condition on a related table

Question

class Inventory(models.Model):
    ...
    product = models.ForeignKey('Product')
    quantity = models.IntegerField(default=0)


class Order(models.Model):
    ...
    quantity = models.IntegerField()
    inventory = models.ForeignKey('Inventory')
    active = models.BooleanField(default=True)

# Not considering the orders that are not active
queryset = Inventory.objects.annotate(
    used=Sum('order__quantity')
).filter(product=product)

I need to get queryset of inventory that has annotated 'used' value. 'used' value is determined by quantity of all related orders but are active.

Edit: To be more precise, I need to SUM quantity of only active orders.

catherine · Answer 1 · 2013-03-21T06:55:38.273

2

queryset = Inventory.objects.filter(product=product, 
    order__active=True).aggregate(
    Sum('order__quantity'))

sum = queryset['order__quantity__sum']

edited Mar 21 '13 at 06:55

answered Mar 21 '13 at 04:06

catherine

22,492
12
61
85

This filters Inventory for which orders are ONLY active. Perhaps my question is not clear. Sorry for that. What I'm looking for is the sum of all 'order_quantity' for orders that are active. Like nesting a filter within the sum. – Nam Ngo Mar 21 '13 at 06:29
That's what you need, right? `I need to get queryset of inventory that has annotated 'used' value. 'used' value is determined by quantity of all related orders but are active.` – catherine Mar 21 '13 at 06:31
ok updated, `sum of all 'order_quantity' for orders that are active` – catherine Mar 21 '13 at 06:57
I need the sum to be annotated. In raw SQL it would *probably* be something like this: `SUM(CASE WHEN "order_invoiceline"."active" = 1 THEN "orders_invoiceline"."quantity" ELSE 0 END)` – Nam Ngo Mar 21 '13 at 07:13

score 1 · Answer 2 · answered Mar 21 '13 at 07:18

I figured out the answer with raw SQL:

SELECT "products_inventory"."id", "products_inventory"."product_id", "products_inventory"."quantity",
SUM(CASE WHEN "orders_order"."active" = True THEN "orders_order"."quantity" ELSE 0 END)
AS "used" FROM "products_inventory"
LEFT OUTER JOIN "orders_order" ON ("products_inventory"."id" = "orders_order"."inventory_id")
WHERE "products_inventory"."product_id" = id_of_product
GROUP BY "products_inventory"."id", "products_inventory"."product_id", "products_inventory"."quantity",
ORDER BY "products_inventory"."id" ASC

Django annotate sum with a condition on a related table

2 Answers2