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 开发者_JS百科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?

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'))

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'))