Django Queryset - How do I do math on related models with prefetch_related?

Question

I have three models:

class Variety(models.Model)
    name = models.CharField(max_length=24)

class Item(models.Model)
    name = models.CharField(max_length=24)
    in_stock = models.IntegerField()

class ItemPart(models.Model)        
    variety = models.ForeignKey(Variety)
    product = models.ForeignKey(Product)
    qty = models.IntegerField()

I would like to tell how much of each Variety has been made into Items by getting all the related ItemParts and multiplying their qty by the in_stock of their related Items.

I've gotten this far:

Variety.objects.all().prefetch_related('itempart_set').values('name').annotate(
    Sum(F("itempart_set__qty") * F("itempart_set__item_set__in_stock")
)

Will this work? Will it sum the products, or will it simply multiply the sums?

Answer 1

You can't do math with models queried by prefetch_related because prefetch_related hits the database separately from the original query. Your queryset is probably evaluated to two database queries and math on the database level is out of the question in that case.

Now, I'm not saying that the above query won't work (Django works its magic and after all, we don't know which version you're using), but I'm saying that the prefetch_related will not give you a performance boost for calculating.

If the above does not work, I would try to do it with subqueries introduced in Django 1.11.