没有关系时计数并返回零的注释 [英] Annotation to count and return zero when there is no relation
问题描述
给出以下关系:
class LicenseRequest:
license_type = models.ForeignKey(LicenseType)
created_at = models.DateField(default=now, editable=False)
class LicenseType:
name = models.CharField(max_length=100)
value = models.CharField(max_length=3, unique=True)
我想计算每个许可证已创建多少个请求类型。但是,由于我正在生成图形,因此在该特定时期内没有任何许可证请求的许可证类型必须包括0(零)。
I want to count how many requests have been created for each license type. However, since I am generating a graphic, I must include 0 (zero) for license types without any license request in that specific period.
我尝试执行建议的操作此处,但此操作无效。我只能从具有多个许可证请求的许可证类型中获得计数。
I tried to do what was suggested here, but it did not work. I can only get the count from License Types which have more than one license request.
qs = LicenseType.objects.filter(
Q(licenserequest__created_at__range=(start_date, end_date)) | Q(licenserequest__isnull=True)
).annotate(rel_count=Count('licenserequest__id'))
我可以找到实现此目标的另一种方法,但我想知道是否可以通过注释来实现。
I could find another way to achieve this goal, but I was wondering if I can do it through annotation.
我正在使用django1.11.15 。
推荐答案
在 djang-2.0 及更高版本, Count
对象的 filter
参数,因此我们可以为此指定代码:
In djang-2.0 and higher, the Count
object has a filter
parameter, so we can specify the codntions for this:
qs = LicenseType.objects.annotate(
rel_count=Count(
'licenserequest',
filter=Q(licenserequest__created_at__range=(start_date, end_date))
)
)
对于 djang-1.11 及以下版本,我们可以使用案例(的
表达式: Sum(..)
..)
For djang-1.11 and below, we can use the Sum(..)
of a Case(..)
expression:
qs = LicenseType.objects.annotate(
rel_count=Sum(Case(
When(
licenserequest__created_at__range=(start_date, end_date),
then=1
),
default=0,
output_field=IntegerField()
))
)
这篇关于没有关系时计数并返回零的注释的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!