How do I use timedelta with a column in my Django ORM query?

Question

I'm using Django and Python 3.7. I have the below two models ...

class Article(models.Model):
    ...
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE, related_name="articles",)
    created_on = models.DateTimeField(default=datetime.now)

class WebPageStat(models.Model):
    ...
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE, related_name="stats", )

    elapsed_time_in_seconds = models.FloatField(default=0)
    score = models.BigIntegerField(default=0)

class Publisher(models.Model):
   name = models.CharField(max_length=100)

   def __str__(self):
       return self.name

I want to write a Django ORM query where given a publisher and an elapsed time in seconds (a WebPageStat record), I find all articles whose "created_on" date is not older than the elapsed time in seconds. Many have suggested using "timedelta," in other posts, but that doesn't seem to be working for me here ...

Article.objects.filter(created_on__lte=datetime.now(timezone.utc) - timedelta(hours=0, minutes=0, seconds=publisher__stats__elapsed_time_in_seconds))
Traceback (most recent call last):
  File "<input>", line 1, in <module>
NameError: name 'publisher__stats__elapsed_time_in_seconds' is not defined

Can I use timedelta with SQL column logic? Otherwise how do I do this?

Please check if this works for you https://stackoverflow.com/a/46297988/1637351 — schillingt, Jan 22 '19 at 18:11
Thanks for this suggestion. I looked at the answer highlighted by your link but I'm still unclear as to why I get this "name is not defined" error. — Dave, Jan 22 '19 at 21:01
The values you pass into the kwargs to a call to `filter` are static values. The only way to reference another field is to use a `F` instance. The code you've written is to create a `timedelta` within postgres where the seconds value is a column from a joined table. That's difficult to specify in an ORM, so there are different classes you need to use. Unfortunately the case you're attempting is complicated to the point where you need to change your approach to the comparison entirely so that you can get the same data, but in a different manner. — schillingt, Jan 22 '19 at 21:54

score 3 · Accepted Answer · answered Jan 25 '19 at 17:46

The root problem here is that you don't understand why you are getting the error

name 'publisher__stats__elapsed_time_in_seconds' is not defined`.

Let's look at the code again:

Article.objects.filter(created_on__lte=datetime.now(timezone.utc) -
    timedelta(hours=0, minutes=0, seconds=publisher__stats__elapsed_time_in_seconds))

In the code above the symbol publisher__stats__elapsed_time_in_seconds is interpreted as a variable reference, which is not defined in your code. If you added

publisher__stats__elapsed_time_in_seconds = 1

just before the code snippet above, then you would not get the error. (You would also not get the results you want.) You're expecting Django's ORM to operate on the symbol publisher__stats__elapsed_time_in_seconds but before Django can get to it, the Python interpreter must interpret the code, and the way the code is written, that's just a variable name which the interpreter must resolve. Django does not get a chance to even see it.

Ok so the way to prevent the interpreter to interpret the name as a variable reference, and have Django ORM's process the name, is to use F() expressions. So you'd be tempted to just do this:

Article.objects.filter(created_on__lte=datetime.now(timezone.utc) -
    timedelta(hours=0, minutes=0, seconds=F("publisher__stats__elapsed_time_in_seconds")))

But then you'd be passing to timedelta a parameter it does not know how to handle.

As schillingt pointed out in a comment, an answer elsewhere by Lutz Prechelt shows how to move the F() expression outside the timedelta. In your case, you could do this:

Article.objects.filter(created_on__lte=datetime.now(timezone.utc) -
        timedelta(seconds=1) * F("publisher__stats__elapsed_time_in_seconds"))))

score 0 · Answer 2 · answered Jan 24 '19 at 00:25

Based on your publisher model, there is a problem on how you are defining the field to compare with. Your field in the question is

publisher__stats__elapsed_time_in_seconds

Your query code is

Article.objects.filter(created_on__lte=datetime.now(timezone.utc) - timedelta(hours=0, minutes=0, seconds=publisher__stats__elapsed_time_in_seconds)).

This basically means get me all articles that have a creation time(created_on field) before or exactly equal to the time(elapsed_time_in_seconds field) for the article-->publisher-->stats record.

This means it looks for a elapsed_time_in_seconds field on the stats object which should be related to your publisher object via a FK or some other way. The publisher object in turn needs to be related to your article object.

Your Article model is related to the Publisher model, but there does not seem to be a relation between the Publisher and the Stats (??). Can you check the stats model to see that the field and the relation is defined correctly?

Hi, Thanks for this response. Article does not directly relate to WebPageStat -- it only relates because they both have a "publisher" field. So in normal SQL you could do a join through that field (where article.publisher = stat.publisher and ...). Still, I gave your suggestion a whirl, however, but it results in the error, "NameError: name 'publisher__stats__elapsed_time_in_seconds' is not defined". — Dave, Jan 24 '19 at 21:12

How do I use timedelta with a column in my Django ORM query?

2 Answers2