Annotation to count and return zero when there is no relation

Question

Given the following 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)

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.

I am using django1.11.15.

Answer 1

In django-2.0 and higher, the Count object has a filter parameter, so we can specify the conditions for this:

qs = LicenseType.objects.annotate(
    rel_count=Count(
        'licenserequest',
        filter=Q(licenserequest__created_at__range=(start_date, end_date))
    )
)

For django-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()
    ))
)

Answer 2

qs = LicenseType.objects.annotate(count=Count('licenserequest__id')
condition = Q(licenserequest__created_at__range=(start_date, end_date)) & Q(licenserequest__isnull=True)
qs = qs.annotate(Case(When(condition, then=F('count')), default=0, output_field=IntegerField())

This should work for the model description that you have provided. To do the later filter, you cannot use a direct .filter() but rather use a Case/When .annotate() clause