对于 count 和 group by，Django 等效

我有一个这样的模型:

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


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

我希望为每个类别选择项的计数(只是计数) ，所以在 SQL 中它会像下面这样简单:

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

有没有类似于“姜戈方式”的方法？还是纯 SQL 是唯一的选择？我对 Django 中的 计()方法很熟悉，但是我不知道 分组如何适合那里。

64312 次浏览
小开

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

The core aggregation support was already there in 1.0; it's just undocumented, unsupported, and doesn't have a friendly API on top of it yet. But here's how you can use it anyway until 1.1 arrives (at your own risk, and in full knowledge that the query.group_by attribute is not 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 you then iterate over query_set, each returned value will be a dictionary with a "category" key and a "count" key.

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

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

小开

How's this? (Other than slow.)

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

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

Edit.

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

counts = defaultdict(int)
for i in Item.objects.all():
counts[i.category] += 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'))
小开

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.


提交答案