CODEWITHSUNDEEP
pythondjangoormdjango-annotate
Tiancheng Liu
Tiancheng Liu

Reputation: 873

Django annotate data by date for empty result

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?

Upvotes: 9

Views: 1212

Answers (2)

sandeepsinghnegi
sandeepsinghnegi

Reputation: 292

You can try this:

Define your date range

start_date = datetime(2018, 7, 22).date()
end_date = datetime(2018, 7, 25).date()

Annotate the queryset to count the occurrences of each date

annotated_queryset = A.objects.annotate(
    date=TruncDate('created')
).values('date').annotate(
    count=Count('id')
).order_by('date')

Generate a list of dates within the date range

date_objects = [start_date + timedelta(days=i) for i in range((end_date - start_date).days + 1)]

Create a dictionary mapping dates to counts from the annotated queryset

count_dict = {entry['date']: entry['count'] for entry in annotated_queryset}

Create a list of dictionaries containing all dates within the range, with their respective counts, filling in missing dates with a count of zero

result = [
    {'date': date.strftime('%Y-%m-%d'), 'count': count_dict.get(date, 0)}
    for date in date_objects
]

Upvotes: 0

Dmytro Ivashchenko
Dmytro Ivashchenko

Reputation: 363

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

Upvotes: 0

Related Questions