如何过滤对象以在 Django 中进行计数注释? [英] How to filter objects for count annotation in Django?

问题描述

考虑简单的 Django 模型 Event 和 Participant:

Consider simple Django models Event and Participant:

class Event(models.Model):
    title = models.CharField(max_length=100)

class Participant(models.Model):
    event = models.ForeignKey(Event, db_index=True)
    is_paid = models.BooleanField(default=False, db_index=True)

使用参与者总数对事件查询进行注释很容易:

It's easy to annotate events query with total number of participants:

events = Event.objects.all().annotate(participants=models.Count('participant'))

如何用is_paid=True过滤的参与者数量进行注释?

How to annotate with count of participants filtered by is_paid=True?

我需要查询所有事件，而不考虑参与者的数量，例如我不需要按注释结果过滤.如果有 0 参与者，那没关系，我只需要注释值中的 0.

I need to query all events regardless of number of participants, e.g. I don't need to filter by annotated result. If there are 0 participants, that's ok, I just need 0 in annotated value.

示例from documentation 在这里不起作用，因为它从查询中排除对象，而不是用 0 注释它们.

The example from documentation doesn't work here, because it excludes objects from query instead of annotating them with 0.

更新. Django 1.8 有新的 条件表达式功能，所以现在我们可以这样做:

Update. Django 1.8 has new conditional expressions feature, so now we can do like this:

events = Event.objects.all().annotate(paid_participants=models.Sum(
    models.Case(
        models.When(participant__is_paid=True, then=1),
        default=0,
        output_field=models.IntegerField()
    )))

更新 2. Django 2.0 有新的 条件聚合 功能，请参阅下面的已接受的答案.

Update 2. Django 2.0 has new Conditional aggregation feature, see the accepted answer below.

更新 3.对于 Django 3.x，请检查下面的答案.>

Update 3. For Django 3.x please check this answer below.

推荐答案

条件聚合 在 Django 2.0 中允许您进一步减少过去的 faff 数量.这也将使用 Postgres 的 filter 逻辑，这比 sum-case 稍微快一些(我见过像 20-30% 这样的数字在周围徘徊).

Conditional aggregation in Django 2.0 allows you to further reduce the amount of faff this has been in the past. This will also use Postgres' filter logic, which is somewhat faster than a sum-case (I've seen numbers like 20-30% bandied around).

无论如何，就您而言，我们正在考虑以下简单的事情:

Anyway, in your case, we're looking at something as simple as:

from django.db.models import Q, Count
events = Event.objects.annotate(
    paid_participants=Count('participants', filter=Q(participants__is_paid=True))
)

文档中有一个单独的部分关于过滤关于注释.它与条件聚合相同，但更像我上面的示例.无论哪种方式，这都比我之前做的粗糙的子查询健康得多.

There's a separate section in the docs about filtering on annotations. It's the same stuff as conditional aggregation but more like my example above. Either which way, this is a lot healthier than the gnarly subqueries I was doing before.

这篇关于如何过滤对象以在 Django 中进行计数注释?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！