Annotate Django queryset with previous Sunday based on row's date field

Question

I have a table with a set of Orders that my customers made (purchased, to say so).

The customers can choose the delivery date. That value is stored in each Order in a field called Order.delivery_date (not too much inventive there)

class Order(BaseModel):
    customer = ForeignKey(Customer, on_delete=CASCADE, related_name='orders')
    delivery_date = DateField(null=True, blank=True)

I would like to annotate a queryset that fetches Orders with the previous Sunday for that delivery_date (mostly to create a weekly report, "bucketized" per week)

I thought "Oh! I know! I'll get the date index in the week and I'll subtract a datetime.timedelta with the number of days of that week index, and I'll use that to get the Sunday (like Python's .weekday() function)":

from server.models import *
import datetime
from django.db.models import F, DateField, ExpressionWrapper
from django.db.models.functions import ExtractWeekDay

Order.objects.filter(
    delivery_date__isnull=False
).annotate(
    sunday=ExpressionWrapper(
        F('delivery_date') - datetime.timedelta(days=ExtractWeekDay(F('delivery_date')) + 1),
        output_field=DateField()
    )
).last().sunday

But if I do that, I get a TypeError: unsupported type for timedelta days component: CombinedExpression when trying to "construct": the timedelta expression.

Not using the F function in the Extract doesn't make a difference either: I get the same error regardless of whether I use Extract(F('delivery_date')) or Extract('delivery_date')

This is a Python 3.4, with Django 2.0.3 over MySQL 5.7.21

I know that I can always fetch the Order object and do this in Python (I even have a little helper function that would do this) but it'd be nice to fetch the objects with that annotation from the DB (and also for learning purposes)

Thank you in advance.

Answer 1

Oh, I had forgotten about extra

It looks like this should do (at least for MySQL)

orders_q = Order.objects.filter(
    delivery_date__isnull=False
).extra(
    select={
        'sunday': "DATE_ADD(`delivery_date`, interval(1 - DAYOFWEEK(`delivery_date`)) DAY)"
    },
).order_by('-id')

It seems to work:

for record in orders_q.values('sunday', 'delivery_date'):
    print("date: {delivery_date}, sunday: {sunday} is_sunday?: {is_sunday}".format(
        is_sunday=record['sunday'].weekday() == 6, **record)
    )

---

date: 2018-06-04, sunday: 2018-06-03 is_sunday?: True
date: 2018-05-30, sunday: 2018-05-27 is_sunday?: True
date: 2018-05-21, sunday: 2018-05-20 is_sunday?: True
date: 2018-06-04, sunday: 2018-06-03 is_sunday?: True

EDIT: Apparently, extra is on its way to being deprecated/unsupported. At the very least, is not very... erm... fondly received by Django developers. Maybe it'd be better using RawSQL instead. Actually, I was having issues trying to do further filter in the sunday annotation using extra which I'm not getting with the RawSQL method..

This seems to work better:

orders_q = orders_q.annotate(
    sunday=RawSQL("DATE_ADD(`delivery_date`, interval(1 - DAYOFWEEK(`delivery_date`)) DAY)", ())
)

Which allows me to further annotate...

orders_q.annotate(sunday_count=Count('sunday'))

I'm not sure why, but when I was using extra, I'd get Cannot resolve keyword 'sunday' into field