How to query as GROUP BY in Django?

ghz 1years ago ⋅ 3253 views

Question

I query a model:

Members.objects.all()

And it returns:

Eric, Salesman, X-Shop
Freddie, Manager, X2-Shop
Teddy, Salesman, X2-Shop
Sean, Manager, X2-Shop

What I want is to know the best Django way to fire a group_by query to my database, like:

Members.objects.all().group_by('designation')

Which doesn't work, of course. I know we can do some tricks on django/db/models/query.py, but I am just curious to know how to do it without patching.

Answer

If you mean to do aggregation you can use the [aggregation features of the ORM](http://docs.djangoproject.com/en/stable/topics/db/aggregation/#topics-db- aggregation):

from django.db.models import Count
result = (Members.objects
    .values('designation')
    .annotate(dcount=Count('designation'))
    .order_by()
)

This results in a query similar to

SELECT designation, COUNT(designation) AS dcount
FROM members GROUP BY designation

and the output would be of the form

[{'designation': 'Salesman', 'dcount': 2}, 
 {'designation': 'Manager', 'dcount': 2}]

If you don't include the order_by(), you may get incorrect results if the default sorting is not what you expect.

If you want to include multiple fields in the results, just add them as arguments to values, for example:

    .values('designation', 'first_name', 'last_name')

References:

#django