CODEWITHSUNDEEP
pythondjangodjango-models
mhega
mhega

Reputation: 77

Django ORM filtering using expression against multiple fields

I am trying to use Django ORM filter to retrieve Announcement records with announcement_dates that are later than the current date - "expire_in_days" field - i.e., retrieve announcements that have not yet expired.

Here is the SQL representation of what I am trying to do with ORM:

select *
from announcement
where message_date > current_date - expire_in_days;

I am able to retrieve all records and filter them externally, but I am trying to learn how to do this with Django.

Here is my model

class Announcement(models.Model):
    message = models.TextField("announcement message")
    message_date = models.DateField("Announcement date")
    expire_in_days = models.PositiveIntegerField("Number of days before announcement expires")

    def __str__(self) -> str:
        return '(message: %s, message_date: %s, expire_in_days: %s)' % (self.message, self.message_date, self.expire_in_days)

Here is what I tried but it did not work:

In [1]: from events.models import Announcement

In [2]: from datetime import date, timedelta

In [3]: from django.db.models import F

In [4]: date.today()
Out[4]: datetime.date(2022, 5, 29)

In [5]: date.today() - timedelta(days=2)
Out[5]: datetime.date(2022, 5, 27)

In [6]: Announcement.objects.all()
Out[6]: <QuerySet [<Announcement: (message: There is a surprise coming up!, message_date: 2022-05-27, expire_in_days: 1)>]>"

In [7]: Announcement.objects.filter(message_date__gt =  (date.today() - timedelta(days = F('expire_in_days'))))
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Input In [7], in <cell line: 1>()
----> 1 Announcement.objects.filter(message_date__gt =  (date.today() - timedelta(days = F('expire_in_days'))))

TypeError: unsupported type for timedelta days component: F

I assumed I could simply do this, but for some reason it did not even fail, yet it did not filter out the record as I would expect

In [8]: Announcement.objects.filter(message_date__gt =  date.today() - F('expire_in_days'))
Out[8]: <QuerySet [<Announcement: (message: There is a surprise coming up!, message_date: 2022-05-27, expire_in_days: 1)>]>

I managed to do it this way as a workaround:

[_ for _ in Announcement.objects.all() if _.message_date+timedelta(days=_.expire_in_days) > date.today()]

Upvotes: 1

Views: 62

Answers (1)

lucutzu33
lucutzu33

Reputation: 3700

Announcement.objects.filter(
    message_date__gt=date.today() - timedelta(days=1)*F('expire_in_days')
)

This should work with PostgreSQL

Upvotes: 2

Related Questions