[ACCEPTED]-Django equivalent for count and group by-django

Here, as I just discovered, is how to do 1 this with the Django 1.1 aggregation API:

from django.db.models import Count
theanswer = Item.objects.values('category').annotate(Count('category'))

(Update: Full ORM aggregation support is now included 28 in Django 1.1. True to the below warning about using 27 private APIs, the method documented here 26 no longer works in post-1.1 versions of 25 Django. I haven't dug in to figure out 24 why; if you're on 1.1 or later you should 23 use the real aggregation API anyway.)

The core aggregation 22 support was already there in 1.0; it's just 21 undocumented, unsupported, and doesn't have 20 a friendly API on top of it yet. But here's 19 how you can use it anyway until 1.1 arrives 18 (at your own risk, and in full knowledge 17 that the query.group_by attribute is not 16 part of a public API and could change):

query_set = Item.objects.extra(select={'count': 'count(1)'}, 
                               order_by=['-count']).values('count', 'category')
query_set.query.group_by = ['category_id']

If 15 you then iterate over query_set, each returned 14 value will be a dictionary with a "category" key 13 and a "count" key.

You don't have 12 to order by -count here, that's just included 11 to demonstrate how it's done (it has to 10 be done in the .extra() call, not elsewhere 9 in the queryset construction chain). Also, you 8 could just as well say count(id) instead 7 of count(1), but the latter may be more 6 efficient.

Note also that when setting .query.group_by, the 5 values must be actual DB column names ('category_id') not 4 Django field names ('category'). This is 3 because you're tweaking the query internals 2 at a level where everything's in DB terms, not 1 Django terms.

Since I was a little confused about how 20 grouping in Django 1.1 works I thought I'd 19 elaborate here on how exactly you go about 18 using it. First, to repeat what Michael 17 said:

Here, as I just discovered, is how 16 to do this with the Django 1.1 aggregation 15 API:

from django.db.models import Count
theanswer = Item.objects.values('category').annotate(Count('category'))

Note also that you need to from django.db.models import Count!

This will 14 select only the categories and then add 13 an annotation called category__count. Depending on the 12 default ordering this may be all you need, but if the default ordering uses a field other than category this will not work. The 11 reason for this is that the fields required 10 for ordering are also selected and make 9 each row unique, so you won't get stuff 8 grouped how you want it. One quick way to 7 fix this is to reset the ordering:

Item.objects.values('category').annotate(Count('category')).order_by()

This should 6 produce exactly the results you want. To 5 set the name of the annotation you can use:

...annotate(mycount = Count('category'))...

Then 4 you will have an annotation called mycount in the 3 results.

Everything else about grouping was 2 very straightforward to me. Be sure to check 1 out the Django aggregation API for more detailed info.

How's this? (Other than slow.)

counts= [ (c, Item.filter( category=c.id ).count()) for c in Category.objects.all() ]

It has the 4 advantage of being short, even if it does 3 fetch a lot of rows.

Edit.

The one query version. BTW, this 2 is often faster than SELECT COUNT(*) in the database. Try 1 it to see.

counts = defaultdict(int)
for i in Item.objects.all():
    counts[i.category] += 1