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.

Thanks

From stackoverflow

• 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

  simplyharsh : Kind of round-trip solution. I would have used it, if i had some extended use of that. But here i just need the number of members per designation thats all.

  Van Gale : No problem. I thought about mentioning 1.1 aggregation features but made the assumption you were using the release version :)

  simplyharsh : Thanks though. +1 already for those useful links.

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

  simplyharsh : Can you please tell me how to do it using order_by??

  Michael : Hi, if you are not using aggregation you could emulate group_by by using an order_by and eliminate the entries you don't need. Of course, this is an emulation and is only useable when using not a lot of data. Since he didn't speak of aggregation, I thought it could be a solution.

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

  simplyharsh : You wont believe i was juggling with same piece of code currently. Yeah 1.1 has quite a good things to watch.