Django - Add Nearest Monday to Queryset

Question

I have an Order model like so:

class Order(models.Model):

    created_at = models.DateTimeField(...)

An order can be created at any time, but all orders get shipped out on the following Monday.

How can I add an extra field to my orders queryset called assembly_date that reflects the next Monday (date the order should be shipped)?

I tried creating a custom OrderManager like so, but am not sure how to correctly set the assembly_date:

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

class OrderManager(models.Manager):

    def get_queryset(): 
    
        # need help with logic here:
        return self.super().get_queryset().annotate(
            assembly_date = ExpressionWrapper(
                F("created_at") - ExtractWeekDay("created_at"),
                output_field = DateField(),
            )
        )

But this operation results in the following error:

django.db.utils.ProgrammingError: operator does not exist: timestamp with time zone - double precision
LINE 1: ...E NULL END) * 2628000.0) * INTERVAL '1 seconds')) - EXTRACT(...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Keep in mind, I want to be able to filter all orders based on their assembly_date.

Correct and if it's a Monday then the `assembly_date` should be the same as the `created_at` date. — Daniel, Dec 14 '20 at 00:42
Use https://docs.djangoproject.com/en/3.1/ref/models/database-functions/#django.db.models.functions.ExtractWeekDay and subtract it from date — iklinac, Dec 14 '20 at 01:03
Check this answer https://stackoverflow.com/questions/6558535/find-the-date-for-the-first-monday-after-a-given-a-date — Gabbeh, Dec 14 '20 at 01:22
maybe can use ```date.isoweekday``` https://docs.python.org/3/library/datetime.html#datetime.date.isoweekday — ha-neul, Dec 14 '20 at 01:24
Thanks for your comments - I am still stuck - I've tried some of the methods recommended in these comments. Above, my edited answer shows the error I'm running into presently. — Daniel, Dec 14 '20 at 01:48
if yo open to having another field in your model as ```assembly_date```, it will be much simpler. — ha-neul, Dec 14 '20 at 02:06

ha-neul · Answer 1 · 2020-12-14T02:42:17.743

Basically you need to dynamically generate timedelta inside of annotate. But as far as I know, there is no way you can apply isoweekday() to a datetimefield inside of annotate.

You can have another field as assembly_date in your model, and use it directly to query.

from datetime import timedelta, date

class Order(models.Model):

    created_at = models.DateTimeField(...)
    assembly_date = models.DateTimeField(...)

    def save(self, *args, **kwargs):
       weekday = self.created_at.isoweekday() # 1 is Monday, 2 is Tuesday.
       daysdiff = 8 - weekday
       self.assembly_date = self.created_at + timedelta(days= daysdiff)
       super(Order, self).save(*args, **kwargs)

Django - Add Nearest Monday to Queryset

1 Answers1