How to group by AND aggregate with Django

django django-aggregation
Guy Bowden picture Guy Bowden · Nov 15, 2012 · Viewed 68k times · Source

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

Aamir Adnan picture Aamir Adnan · Nov 15, 2012

Try this:

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

Related questions

Django: Group by date (day, month, year)

I've got a simple Model like this: class Order(models.Model): created = model.DateTimeField(auto_now_add=True) total = models.IntegerField() # monetary value And I want to output a month-by-month breakdown of: How many sales there were in a month (…

Read More
How to filter objects for count annotation in Django?

Consider simple Django models Event and Participant: class Event(models.Model): title = models.CharField(max_length=100) class Participant(models.Model): event = models.ForeignKey(Event, db_index=True) is_paid = models.BooleanField(default=False, db_index=True) It's easy to annotate …

Read More
Django 1.11 Annotating a Subquery Aggregate

This is a bleeding-edge feature that I'm currently skewered upon and quickly bleeding out. I want to annotate a subquery-aggregate onto an existing queryset. Doing this before 1.11 either meant custom SQL or hammering the database. Here's the documentation for this, …

Read More