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?

Hi, maybe try this: https://stackoverflow.com/questions/52290430/annotation-to-count-and-return-zero-when-there-is-no-relation — Marek Naskret, Jun 28 '20 at 14:46
Does this answer your question? [Django + PostgreSQL: Fill missing dates in a range](https://stackoverflow.com/questions/31682737/django-postgresql-fill-missing-dates-in-a-range) — DJ Ramones, Jan 04 '21 at 17:43
@MarekNaskret the answer there is not applicable because that question is for the case when there are related models. (The “when there is no relation” part of the question title is misleading.) This question is in the context of a single model, and the query can only work with the same model's attributes. — DJ Ramones, Jan 04 '21 at 17:49

score 0 · Answer 1 · answered May 10 '22 at 15:00

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')

Django annotate data by date for empty result

1 Answers1

Linked