django ORM: make query

Question

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

Answer 1

Check http://stackoverflow.com/questions/327807/django-equivalent-for-count-and-group-by

Answer 2

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