CODEWITHSUNDEEP
djangodjango-orm
Philipp S.
Philipp S.

Reputation: 981

Django: Annotate missing dates

I want to know how many orders I got per day in a specific period.

My Order model looks like that:

class Order(models.Model):

...
    bookingdate         = models.DateTimeField(default=timezone.now)
...

My current Django "query" looks like that:

query = Order.objects.filter(bookingdate__gte=startdate, bookingdate__lte=enddate)
query = query.annotate(created_date=TruncDate('bookingdate')).values('created_date').annotate(
            sum=Count('created_date')).values('created_date', 'sum')

My current problem is that I only get dates listed where at least one order happend per day. But I want also to list the dates with 0 orders.

For example I get right now a "list" like that:

12.12.2018 => 3
14.12.2018 => 1
17.12.2018 => 2

But I also want the dates in between. Example:

12.12.2018 => 3
13.12.2018 => 0
14.12.2018 => 1
15.12.2018 => 0
16.12.2018 => 0
17.12.2018 => 2

Any idea how I can do this?

Upvotes: 10

Views: 485

Answers (2)

andzep
andzep

Reputation: 2061

Because the 'empty' days don't exist on the existing orders, there's no way you can include them on the query, so you'll always have to generate those individually.

So I'd suggest to add a class method in the Order model, that would return you a tuple of (<day>, <orders for that day>). This method could take an optional parameter that would fill up the empty days between the first and last day of the existing orders. E.g.

from django.db import models
from django.db.models import Count
from django.db.models.functions import TruncDate
from datetime import timedelta

class Order(models.Model):

    @classmethod
    def get_orders_by_day(cls, include_empty_days=False):
        # A list of tuples of all existing orders grouped and sorted by date
        ret = list(
            cls.objects.annotate(created_date=TruncDate('bookingdate'))
            .values('created_date')
            .annotate(count=Count('created_date'))
            .order_by('created_date')
            .values_list('created_date', 'count')
        )

        # If including empty days (without orders), fill up all missing days 
        # between the first and last date from the previous list.
        if include_empty_days and ret:
            first_day = ret[0][0]
            last_day = ret[-1][0]
            all_days = [first_day + timedelta(days=i) for i in range((last_day - first_day).days + 1)]

            orders_dict = dict(ret) # create a dict with the days as key
            return [(day, orders_dict.get(day, 0)) for day in all_days]

        return ret

Upvotes: 0

Amine Messaoudi
Amine Messaoudi

Reputation: 2299

You have a typo in your code

Replace

query = Order.objects.filter(bookingdata__gte=startdate, bookingdate__lte=enddate)

With

query = Order.objects.filter(bookingdate__gte=startdate, bookingdate__lte=enddate)

Upvotes: -3

Related Questions