How to group by AND aggregate with Django

Question

I have a fairly simple query I'd like to make via the ORM, but can't figure that out..

I have three models:

Location (a place), Attribute (an attribute a place might have), and Rating (a M2M 'through' model that also contains a score field)

I want to pick some important attributes and be able to rank my locations by those attributes - i.e. higher total score over all selected attributes = better.

I can use the following SQL to get what I want:

select location_id, sum(score)      from locations_rating      where attribute_id in (1,2,3)      group by location_id order by sum desc; 

which returns

 location_id | sum  -------------+-----           21 |  12            3 |  11 

The closest I can get with the ORM is:

Rating.objects.filter(     attribute__in=attributes).annotate(     acount=Count('location')).aggregate(Sum('score')) 

Which returns

{'score__sum': 23} 

i.e. the sum of all, not grouped by location.

Any way around this? I could execute the SQL manually, but would rather go via the ORM to keep things consistent.

Thanks

Answer 1

Try this:

Rating.objects.filter(attribute__in=attributes) \     .values('location') \     .annotate(score = Sum('score')) \     .order_by('-score')