[SOLVED] Django queryset count with zero

Django queryset count with zero

I have this Django Model :

class Survey(models.Model):

    call = models.OneToOneField(Call, null=True, related_name='survey', on_delete=models.CASCADE)
    service = models.ForeignKey(Service, related_name='survey', null=True, on_delete=models.CASCADE)
    when_start = models.DateTimeField()
    when_end = models.DateTimeField()

I would like to obtain the number of calls per service over a time slot

I tried this :

start = datetime.datetime(2024, 6, 17, 0, 0)
end = datetime.datetime(2024, 6, 17, 23, 59, 59)
services = <QuerySet [<Service: S1>, <Service: S2>, <Service: S3>]>

Survey.objects.filter(service__in=services, when_start__range=(start, end)).values('service__name').annotate(nb=Count('call'))

result : <QuerySet [{'service__name': 'S1', 'nb': 50}, {'service__name': 'S2', 'nb': 119}]>

But I would like to have this (0 for "S3" service):

<QuerySet [{'service__name': 'S1', 'nb': 50}, {'service__name': 'S2', 'nb': 119}, {'service__name': 'S3', 'nb': 0}]]>

I read several notes that referred to "Coalesce" but I can't seem to use it correctly

Solution

Annotate the service instead:

from django.db.models import Count, Q

services.objects.annotate(
    nb=Count('survey__call', filter=Q(survey__when_start__range=(start, end)))
)

You also get Service objects with an attribute .nb, so not only the name of the Service.