Django annotate data by date for empty result

Question

Suppose I have a object model A, and it has a field called created, which is a datetime type field.

If I use annotate to count how many A are created each day, I can use

A.objects.annotate(date=Trunc('created', 'day', output_field=DateField()) ).values('date').order_by('date').annotate(count=Count('id'))

After that, I can get the result, which looks like

[{date: '2018-07-22', count:1 }, {date: '2018-07-23', count:1 }, {date: '2018-07-25', count:1 }]

However, notice that I miss a 2018-07-24 because it didn't create any A in that day. Is there any way to let result to have {date: '2018-07-24', count:0 } inside that queryset?

Answer 1

My variant for PostgreSQL:

from datetime import date, timedelta
from django.db.models.functions import Trunc
from django.db.models.expressions import Value
from django.db.models import Count, DateField

# A is model

start_date = date(2022, 5, 1)
end_date = date(2022, 5, 10)

result = A.objects\
    .annotate(date=Trunc('created', 'day', output_field=DateField())) \
    .filter(date__gte=start_date, date__lte=end_date) \
    .values('date')\
    .annotate(count=Count('id'))\
    .union(A.objects.extra(select={
             'date': 'unnest(Array[%s]::date[])' %
                     ','.join(map(lambda d: "'%s'::date" % d.strftime('%Y-%m-%d'),
                                  set(start_date + timedelta(n) for n in range((end_date - start_date).days + 1)) -
                                  set(A.objects.annotate(date=Trunc('created', 'day', output_field=DateField())) \
                                               .values_list('date', flat=True))))})\
            .annotate(count=Value(0))\
            .values('date', 'count'))\
    .order_by('date')