Django - left joining date array with a queryset

Question

In my Django project, I have the following model:

class Examine(models.Model):
         name = models.CharField(max_length=45)
         date = models.DateField(blank=True, null=True)

My goal is to come up with a queryset that would enable me to render a template with list of dates and the examines for the date - if any. For example, I have three examines in the DB:

Math - 01 January 2019, 
History - 03 January 2019
Geoghraphy - 03 January 2019

and I am interested in the date interval January 1 to January 4 2019. THe output should be:

January 1
     Math
January 2
     No exams
January 3
     History
     Geography
January 4
     No exams

I have two years of experience with Django ORM, but can't even submit any of my attempts of how I tried to implement this. I have no ideas. Is it possible in Django ?

Anentropic · Accepted Answer · 2021-01-19T11:44:58.007

Start with what you know to be possible - fetch the exams from the db, using a date range filter as described in the docs here.

from datetime import date

start_date = date(2019, 1, 1)
end_date = date(2019, 1, 4)
exams = Examine.objects.filter(date__gte=start_date, date__lte=end_date).order_by('date')

I think perhaps you got stuck on the idea of doing a 'left join' with dates. This would be possible in a raw SQL query but is not necessary here.

Essentially what we will do is perform the left join operation just in Python code after fetching the exams from the relevant date range.

Lets make a data structure that can support what we want to do:

from collections import defaultdict

exams_by_day = defaultdict(list)
for exam in exams:
    exams_by_day[exam.date].append(exam)

Because it's a defaultdict, if we access a key which doesn't exist we will get an empty list.

The last piece is to generate a list of dates for the left column:

from datetime import timedelta

date_diff = end - start  # a timedelta object (3 days)
dates = []
for i in range(date_diff.days + 1):
    dates.append(start + timedelta(days=i))

Finally we can pass both of these data structures into our template and iterate over them to generate the output you wanted:

<dl>
{% for date_ in dates %}
    <dt>{{ date_|date:"F j"}}</dt>
    <dd>
        <ol>
        {% with exams=exams_by_date.date %}
        {% for exam in exams %}
            <li>{{ exam.name }}</li>
        {% empty %}
            <li>No exams</li>
        {% endfor %}
        </ol>
    </dd>
{% endfor %}
</dl>

See https://docs.djangoproject.com/en/2.0/ref/templates/builtins/
for details of with and |date:"F j" above.

for some reason exams are always empty in the template. Does it have anything to do with this (https://stackoverflow.com/questions/32813500/items-not-working-on-defaultdict-in-django-template)? I have tried their suggestions, but still doesn't help :( — Edgar Navasardyan, Jul 21 '18 at 11:58
Did you try to run this code? Is it ok in your case? Python 3.5 and Django 1.10 — Edgar Navasardyan, Jul 21 '18 at 12:00
I haven't tried running it. You need to check if the values in `exams_by_date.keys()` are the same value and type as the `date` objects in `dates` — Anentropic, Jul 22 '18 at 18:10

Django - left joining date array with a queryset

1 Answers1