Django query by separate date and time fields (how to perform conditional order_by)

run_the_race 0
Viewed 30

I have a Meeting model. It has a separate date and time field, the reason why it is not a single datetime field is that the time is allowed to be null. If I used a single datetime field and set the hours/minutes to 00:00 if no time is given, then I can't distinguish that from a meeting set at 00:00. And some unusual sentinel value like 13:37:42 as no meeting is a weird hack.

Here's the model:

class Meeting(models.Model):
    meeting_date = models.DateField(db_index=True)
    meeting_time = models.TimeField(db_index=True, null=True, blank=True)

Now the problem comes in to filter by a given date/time.

So I think the solution is to filter by date, then order by the time. E.g. to get the latest meeting that is before now:

from datetime import datetime

now = datetime.now()
prev_meeting = (
    Meeting.objects.filter(meeting_date__lte=now.date())
    .order_by('-meeting_date', f'meeting_time <= {now.time()}', '-meeting_time')
    .first()
)

The f'meeting_time <= {now.time()}' is the part I don't know how to do with the django ORM, but I know how to do with SQL. the Django Docs do not mention conditional ordering.

django-orm django-models django
1 Answers

The solution I came up with is to use a single datetime field to store the date/time, and then instead of a sentinel time value, I have an additional boolean value storing whether the time was set or not. If the time was not set, then then datetime time is 00:00 and the time_was_set field is set to False.

run_the_race0
Related