Django ORM: Order by dates and weekdays

Question

I have a table that stores courses and one that stores their dates and weekdays:

Course: ID, CourseDates (M2M) 

CourseDate: ID, Date (Date), Weekday (0-6), Full (Boolean)

If its a date, weekday is NULL
If its a weekday, date is NULL
Only dates can be set to full (full=True)

Use case:

I want to order by the next occurence (date or weekday) that is not full.

Challenge:

Weekdays should only be considered if there is no date that is full for that day.

Example:

Course 1

1, Date=2021-02-12, Weekday=NULL, Full=True    
2, Date=NULL, Weekday=4 (Friday), Full=False

Course 2

3, Date=2021-02-13, Weekday=NULL, Full=False

Expected Result:

Course 2
Course 1

Explanation:

Next date for Course 2: 2021-02-13
Next date for Course 1: 2021-02-19 (because 2021-02-12 is full)

Question:

Can this be done using SQL/Django ORM only? Does it even make sense?

My current attempt (this wont work in the above example):

"""
The subquery annotates all CourseDates with the difference to today, orders them by days left,
and in the main query we only use the first entry, which is the closest CourseDate for this course
"""
subquery = (
        CourseDate
            .objects
            .exclude(full=True)
            .filter(course=OuterRef('id'))
            .filter(Q(date__gt=today_date) | Q(date__isnull=True))
            .annotate(today=Value(today_date, output_field=DateField()))
            .annotate(
                days_until_next=Case(
                    When(weekday__lt=today_weekday, then=6 - today_weekday + F("weekday")),
                    When(Q(weekday__gt=today_weekday)), then=F("weekday") - today_weekday),
                    When(Q(weekday=today_weekday)), then=6),
                    When(Q(date__gt=today_date)), then=(F("date") - F("today")) / one_day_in_microseconds),
                    # the difference is a durationfield, which uses microseconds
                    output_field=IntegerField(),
                )
            )
            .exclude(days_until_next__isnull=True)
            .order_by('days_until_next')
    )
    courses = courses.annotate(days_until_next=Subquery(subquery.values('days_until_next')[:1]))
    courses = courses.annotate(
        nulls_last=Case(
            When(days_until_next__isnull=True, then=Value(1)),
            When(days_until_next__isnull=False, then=Value(0)),
            output_field=IntegerField(),
        )
    )

    courses = courses.order_by("days_until_next")

Django ORM: Order by dates and weekdays

0 Answers