Django equivalent for count and group by

Question

I have a model that looks like this:

class Category(models.Model):     name = models.CharField(max_length=60)  class Item(models.Model):     name = models.CharField(max_length=60)     category = models.ForeignKey(Category) 

I want select count (just the count) of items for each category, so in SQL it would be as simple as this:

select category_id, count(id) from item group by category_id 

Is there an equivalent of doing this "the Django way"? Or is plain SQL the only option? I am familiar with the count( ) method in Django, however I don't see how group by would fit there.

Answer 1

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

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

Answer 2

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

Here, as I just discovered, is how to do this with the Django 1.1 aggregation 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 select only the categories and then add an annotation called category__count. Depending on the default ordering this may be all you need, but if the default ordering uses a field other than category this will not work. The reason for this is that the fields required for ordering are also selected and make each row unique, so you won't get stuff grouped how you want it. One quick way to fix this is to reset the ordering:

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

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

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

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

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