Complex annotation & aggregation query

Question

I have a QuerySet of OrderItem objects. They are in the form of:

OrderItem
- ID
- BillingParty
- Rate

I want to aggregate / GROUP BY by QuerySet, such that it orders by the highest combined rate and annotates the rate and count. Here's an illustration

1
Paramount
500

2
WB
300

3
Paramount
400

From this, I want to be able to get:

[
    {'BillingParty': 'Paramount', 'TotalRate': 900, 'ItemCount': 2},
    {'BillingParty': 'WB', 'TotalRate': 300, 'ItemCount': 1},
]

How would I do this? I was thinking it would be something like:

order_items.aggregate('billing_party')
           .annotate(ItemCount=Count('id'), TotalRate=Sum('rate')

Or, is it too complex for django and I need to do a python for loop here to add in the stuff.

score 1 · Accepted Answer · answered Jun 10 '14 at 00:53

This actually turned out to be a bit easier than I thought. By doing .values() and then .annotate(), I was able to do the GROUP BY.

order_items = order_items.values('billing_party')
               .annotate(total_rate=Sum('rate'), count=Count('id'))
               .order_by('-total_rate')

Here's the documentation on doing .values() and then annotate() to handle issues of the above: https://docs.djangoproject.com/en/dev/topics/db/aggregation/#values.

score 0 · Answer 2 · edited May 23 '17 at 10:32

[update]

I was going to suggest exactly that, seems like your question is a dupe: see How to group by AND aggregate with Django

So in the end this is not very complex...

You have a really complex query when you start to realize that the ORM layer in Django is optimized for easy of use instead of flexibility and you have to resort to raw SQL in order to perform some complex queries.

When I'm feeling lazy I just use raw SQL, when not I throw some SQLAlchemy+SQLSoup in the mix - this ORM maps almost 1:1 to raw SQL but is way easier to reuse/refactor compared to raw SQL using string composition.

When I need to extract every bit of performance from the server, I just ask the DBA to create a view with the result I need and point a model to that view using Meta.db_table = 'view_name' (use any unique column as index or use a fake index like a random uuid4).

Complex annotation & aggregation query

2 Answers2