simplyharsh simplyharsh - 2 months ago 13
Python Question

How to query as GROUP BY in django?

I query a model,

Members.objects.all()


and it returns say

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 db, as 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 and are using Django 1.1 (currently in alpha 1), you can use the new aggregation features of the ORM:

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

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}]