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

https://docs.djangoproject.com/en/dev/topics/db/aggregation/ http://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django — felipsmartins, Nov 15 '12 at 18:19
Possible duplicate of [How to query as GROUP BY in django?](https://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django) — Aaron McMillin, Sep 25 '17 at 15:16

score 154 · Accepted Answer · answered Nov 15 '12 at 19:29

154

Try this:

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

answered Nov 15 '12 at 19:29

Aamir Rind

38,793
23
126
164

3

hmmm - annotate not aggregate - why is that? – Guy Bowden Nov 16 '12 at 00:04
64

`aggregate` is for the complete resultset, `annotate` for individual (grouped) rows. – Bouke Jan 16 '13 at 11:36
does anyone know how to workaround `'dict' object has no attribute '_meta'` with such QuerySet? – maciek Sep 16 '16 at 10:50
Hi @Aamir , I have a similar problem, can you have a look at it? [link-here](https://stackoverflow.com/questions/49587375/django-select-nested-collection-avg-with-group-by-in-one-to-many-relationship) – Ermir Beqiraj Apr 01 '18 at 16:15
Returns something like `[{"location": 53, "score": 100}, {"location": 54, "score": 104}]` – Irvan Jun 01 '18 at 11:36
Why i don't have current object in result? – reza_khalafi Oct 29 '22 at 12:43

score 41 · Answer 2 · edited Sep 08 '21 at 06:20

41

Can you try this.

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

edited Sep 08 '21 at 06:20

kozhioyrin

400
4
15

answered Nov 15 '12 at 18:27

Srinivas Reddy Thatiparthy

10,985
4
45
68

How to group by AND aggregate with Django

2 Answers2

Linked

Related