5

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?

Cogsy
  • 5,584
  • 4
  • 35
  • 47

2 Answers2

2

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'))
alukach
  • 5,921
  • 3
  • 39
  • 40
1

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'))
lprsd
  • 84,407
  • 47
  • 135
  • 168
  • Thanks, first time I've seen that technique. – Cogsy May 11 '11 at 11:38
  • I can make this work, but I have to use SUM(price*qty) as it doesn't follow double'_' relationships, and the aggregate can't see the 'lineprice' field. Still less than desirable I'm afraid – Cogsy May 11 '11 at 11:52
  • 11
    This won't work. The `aggregate` can't see things introduced by `extra` (also the `select` expects a dictionary, so it would need `{}` rather than `()`) – greg May 11 '12 at 08:16
  • This doesn't work. Why would you post a solution that doesn't work. – Nicholas Hamilton Mar 30 '14 at 01:22