How can I use the F() object to do this with the Django ORM?

Question

I encountered a model like this:

class Task(models.Model):
    timespan = models.IntegerField(null=True, blank=True)

class Todo(models.Model):
    limitdate = models.DateTimeField(null=True, blank=True)
    task = models.ForeignKey(Task)

I need to extract all Todos with a limitdate that is lower or equal to today's date + a timespan defined in the related Task model.

Something like (dummy example):

today = datetime.datetime.now()
Todo.objects.filter(limitdate__lte=today + F('task__timespan'))

Now, I can do that with a loop but I'm looking for a way to do it with F(), and I can't find one.

I'm starting to wonder if I can do that with F(). Maybe I should use extra ?

Please note that I don't have the luxury of changing the model code.

Answer 1

The main issue is that DB does not support date + integer and its hard to write ORM query to date + integer::interval, for PostgreSQL for example, where integer is the value of the task_timespan column, in days count.

However, as
limitdate <= today + task__timespan equals to
limitdate - today <= task__timespan

We could transform the query to

Todo.objects.filter(task__timespan__gte=F('limitdate') - today).distinct()

thus the SQL becomes something like integer >= date - date, that should work in PostgreSQL because date - date outputs interval which could be compared w/ integer days count.

In other DBs such as SqLite, it's complicated because dates need to be cast w/ julianday() at first...and I think you need to play w/ extra() or even raw() to get the correct SQL.

Also, as Chris Pratt suggests, if you could use timestamp in all relative fields, the query task might become easier because of less limited add and subtract operations.

P.S. I don't have env to verify it now, you could try it first.

Answer 2

The problem is that there's no TIMESPAN type on a database. So, F cannot return something that you can actually work with in this context. I'm not sure what type of field you actually used in your database, but the only way I can think of to do this is to the store the timespan as an integer consisting of seconds, add that to "today" as a timestamp, and then convert it back into a datetime which you can use to compare with limitdate. However, I'm unsure if Django will accept such complex logic with F.