How to query as GROUP BY in django?

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 1

If you mean to do aggregation you can use the aggregation features of the ORM:

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 documentation: values(), annotate(), and Count
• Django documentation: Aggregation, and in particular the section entitled Interaction with default ordering or order_by()