Django filter by date range and if no records in a date, return dummy records in that without being dead in loop

Question

I have a very unique requirement and I have not noticed such a solution on Django documentation.

Also, I don't want to use any loop after the database query even though I can achieve such a solution with loop over all the records.

class Example(models.Model):
    date = DateField
    name = CharField

and Let's say i have following records in the table

[
 {date: "2018-01-05", name="jhon doe"},
 {date: "2018-01-11", name="jonathan someone"},
 {date: "2018-01-21", name="someone developer"},
]

and my query:

Example.objects.filter(date_range=["2018-01-01", "2018-01-31"])

As normal when we query with a date range, it returns all the records within that range. it's expected and normal.

But I want it should come with a blank record when there are no records on a certain date range.

As we notice I have only 3 records in that range, so I am expecting a result like this

[
 {date: "2018-01-01", name="Not Found"},
 {date: "2018-01-02", name="Not Found"},
 {date: "2018-01-03", name="Not Found"},
 {date: "2018-01-04", name="Not Found"},
 {date: "2018-01-05", name="jhon doe"},
 {date: "2018-01-06", name="Not found"},
 '''''to be continued in a range''''
]

Is there anyone who knows to prepare queryset like above this during filter? I need it like this coz, i am using a javascript tool in the frontend, it expects data like this.

Even though i can achieve this with frontend writing a utility function in frontend or looping over all the queryset and prepare the date above way.

Does anyone have a solution except being sunk in the loop?

I appreciate your constructive answer

Answer 1

I have already made a comment says that it is not possible, of course depending on my knowledge but I want to explain why and how can this be solved.

Lets say you have your Example model class with those 3 records. When you make this query; Example.objects.filter(date_range=["2018-01-01", "2018-01-31"]) it will bring those records in that range like you said but there is no way that it fetches a records like

{date: "2018-01-01", name="Not Found"},
{date: "2018-01-02", name="Not Found"}...

because those objects does not exists in your database actually. I think you have to use a loop to create those records that does not in the db. It can be done as:

# Initialize your start and end date
date_start = datetime.date(2018, 1, 1)
date_end = datetime.date(2018, 1, 31)

# Get only the dates so that we can find what is not in that range
example_dates = Example.objects.values_list('date', flat=True) 

# Initialize new list that will include records does not exists
not_found_dates = []

# Loop through in date range and if date does not exists 
# Create a dict and add it to your list
for i in range(1, (date_start - date_end).days + 1):
    new_date = date_start + datetime.timedelta(days=i)
    if new_date not in dates:
        not_found_dates.append({'date': new_date, 'name': 'Not Found'}) 

# Get your original queryset
examples = Example.objects.filter(date__range=(date_start, date_end)).values('date', 'name')
# Convert it to a list
examples = list(examples)
# Than extends examples with other list
examples.extend(example_dates)

If you want to use your serializer to convert your queryset, instead of creating dictionaries as records you should create objects like example=Example.objects.create(**kwargs) and merge 2 queryset so that your serializer can manipulate it. But this will create new records in your database so it is may not be a good approach also.