How to group by AND aggregate with Django
django, django-aggregation
Solution
Try this:
Rating.objects.filter(attribute__in=attributes) \
.values('location') \
.annotate(score = Sum('score')) \
.order_by('-score')
Problem
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