Search code examples
pythondjangodjango-modelsgroup-by

How to query as GROUP BY in Django?

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.

Solution

  • 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: