How to query as GROUP BY in django??

Question

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.

Thanks

Answer 1

You need to do custom SQL as exemplified in this snippet:

Custom SQL via subquery

Or in a custom manager as shown in the online Django docs:

Adding extra Manager methods

Answer 2

An easy solution, but not in a proper way is to use RAW-SQL:

http://docs.djangoproject.com/en/dev/topics/db/sql/#topics-db-sql

Another solution is to use the group_by property:

query = Members.objects.all().query
query.group_by = ['designation']
results = query.execute_sql()

You can now iterate over the results variable to retrieve your results. Note that group_by is not documented and may be changed in future version of Django.

And... why do you want to use group_by? If you don't use aggregation, you can use order_by to achieve an alike result.

Answer 3

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