Example Group By Having query in Django 1.10.
Let’s say you have SQL such as:
SELECT DISTINCT user_id, COUNT(*) FROM my_model WHERE tag_id = 15 OR tag_id = 17 GROUP BY user_id HAVING COUNT(*) > 1
The above query finds all the user_ids in the my_model table with both tag id 15 and 17.
In Django a Group By Having query is doable, but you have to follow the Django way:
MyModel.objects.filter(row_id__in=[15,17])\
.distinct()\
.values('user_id')\
.annotate(user_count=Count('user_id'))\
.filter(user_count__gt=1)\
.order_by('user_id')
Breaking it down:
MyModel.objects.filter(row_id__in=[15,17])
This should look familiar, __in is a nice shortcut for an OR clause, but that isn’t material to the GROUP BY / HAVING part at all.
.distinct()
Adds the DISTINCT clause. It may not be relevant in your case. I’m doing it to be clear that I don’t want to see the user_id more than once.
.values('user_id')
This is where you specify the column of interest.
.annotate(user_count=Count('user_id'))
The annotate call sets up a named key and specifies the aggregate SQL function (COUNT, SUM, AVERAGE, etc). The key user_count is arbitrary, you can name it whatever you want. It gets used in the next line and is the row dictionary key for the count value (think SELECT COUNT(*) as user_count).
.filter(user_count__gt=1)
This is what makes the HAVING clause appear in the SQL that gets executed. Note that user_count__gt is matching the named key created on the previous line and filtering it for values greater than 1.
.order_by('user_id')
The example SQL above doesn’t have an ORDER BY, but I’ve added it here to point out a quirk in Django. If order_by is left off Django will add the model’s default sort column to the SELECT and GROUP BY sections of the query. This will screw up your results. So, when doing a Group By / Having query in Django, it is a good idea to explicitly specify a sort order even if it doesn’t matter in a logical sense.
It will return something like:
<QuerySet [{'user_count': 2, 'user_id': 1L}, {'user_count': 2, 'user_id': 5L}]>
Trying to get your query working in the shell? This might help: how to turn on SQL logging in the Django shell.