CODEWITHSUNDEEP
djangodjango-models
Cogsy
Cogsy

Reputation: 5642

Product of two fields annotation

I currently have a line in my Django app like this:

db.execute("SELECT SUM(price * qty) FROM inventory_orderline WHERE order_id = %s", [self.id])

I'd rather perform this through the models interface provided my Django, but can't find any references.

I'm pretty sure this can be done with an annotation, but the examples only cover a few of them and I can't find a list in the documentation.

I'd like to do something like this:

self.line_items.annotate(lineprice=Product('orderline__price', 'orderline__qty')).aggregate(Sum('lineprice'))

Can anyone suggest an annotation class to use to perform the multiplication? Even better, a link to the API listing all these annotation/aggregation classes?

Upvotes: 5

Views: 3700

Answers (2)

alukach
alukach

Reputation: 6298

It's not clearly documented, but this can be done with the F() function:

from django.db.models import F

self.line_items.annotate(lineprice=F('orderline__price') * F('orderline__qty')).aggregate(Sum('lineprice'))

Upvotes: 3

lprsd
lprsd

Reputation: 87225

Unfortunately, there aren't enough inbuilt Aggregate functions and specifically, there is not one for Product.

But that doesn't limit you in any way other than having to write a "non-concise" ORM query. Specifically for your case, you should be able to do:

self.line_items.extra(select=("lineprice": "orderline__price*orderline__qty")).aggregate(Sum('lineprice'))

Upvotes: 1

Related Questions