Possible Duplicate:
Django equivalent for count and group by
I'm studying django. In my project I'm having trouble with making a query with django ORM:
SELECT MIN( id ) AS id, domain, COUNT( * ) AS cnt
FROM app_competition
WHERE word_id = 1545
GROUP BY domain
Help me please make this query
You need some Aggregation for this!
What you need to do in your case is two things: First, use values to force GROUPBY on your query, then annotate your query using the built-in functions Min and Count
If my django-orm fu is correct, your query should look something like this:
from django.db.models import Min, Count
Competition.objects.values('domain').filter(word_id=1545).annotate(id=Min('id'), cnt=Count('id'))
Key things to note: You need to place the values call before any annotations to force it to GROUPBY, then the rest is pretty self explanatory.
Didn't test the query so I hope I got it right :)
