Filtering on the count with the Django ORM.

Question

Hello, I have a query that's basically "count all the items of type X, and return the items that exist more than once, along with their counts". Right now I have this:

Item.objects.annotate(type_count=models.Count("type")).filter(type_count__gt=1).order_by("-type_count")

but it returns nothing (the count is 1 for all items). What am I doing wrong?

Ideally, it should get the following:

Type
----
1
1
2
3
3
3

and return:

Type, Count
-----------
1     2
3     3

Answer 1

It's logical error ;)

type_count__gt=1 means type_count > 1 so if the count == 1 it won't be displayed :) use type_count__gte=1 instead - it means type_count >= 1 :)

Answer 2

In order to count the number of occurrences of each type, you have to group by the type field. In Django this is done by using values to get just that field. So, this should work:

Item.objects.values('group').annotate(
     type_count=models.Count("type")
).filter(type_count__gt=1).order_by("-type_count")