How do I write a Django ORM query that searches for a value that is close but doesn't exceed a certain other value?

user_1235929 0
Viewed 71

I'm using Python 3.7 with Postgres 9.5. I would like to write a Django ORM query if possible. I have the below model

class PageStat(models.Model):
    article = models.ForeignKey(Article, on_delete=models.CASCADE, )
    elapsed_time = models.IntegerField(default=0)
    score = models.IntegerField(default=0)

What I would like to do is write a query that, for a given article and elapsed time (an integer in seconds), I would like to write a query taht returns the PageStat object with the greatest elapsed time value that doesn't exceed the argument. So, for example, if my table had these values

article_id      elapsed_time
==================
1           10
1           20
1           30
2           15

And I did a search with article ID "1" and elapsed time" 15", I would like to get back the first row (where article_id = 1 and elapsed_time = 10), since "10" is the greatest value that is still less than 15. I know how to write a query for just finding the stat with the article, 

PageStat.objects.filter(article=article)

but I don't know how to factor in the time value.

python-3.x postgresql orm django python
2 Answers

You could try:

PageStat.objects.filter(elapsed_time__lte=20)

There are also:

lt - less than 
gte - greater than equal
gt - greater than
etc
user_82169620

You can order the QuerySet by elapsed_time, and then select the first one, like:

slowest_pagestat = PageStat.objects.filter(
    article=my_article,
    elapsed_time__lte=threshold
).order_by('-elapsed_time').first()

This will return a PageState object if such PageState exists, and None otherwise.

This will make a query that looks like:

SELECT pagestat.*
FROM pagestat
WHERE pagestat.article_id = 1
AND   pagestat.elapsed_time <= 15
ORDER BY pagestat.elapsed_time DESC
LIMIT 1

with 1 the primary key of my_article, and 15 the threshold.

user_675790
Related