8

I have the following model:

class Foobar(models.Model):
    foo = models.IntegerField()

And I figured out how to calculate the delta of consecutive foo fields by using window functions:

qs = Foobar.objects.annotate(
    delta=F('foo') - Window(
        Lag('foo'),
        partition_by=F('variant'),
        order_by=F('timestamp').asc(),
    )
)

Now I only want the records from this where delta is negative:

qs.filter(delta__lte=0)

But as you might expect, this gives an error:

django.db.utils.NotSupportedError: Window is disallowed in the filter clause.

How can I do this filtering with the Django ORM?

djvg
  • 11,722
  • 5
  • 72
  • 103
Code-Apprentice
  • 81,660
  • 23
  • 145
  • 268
  • 3
    This is known issue as of the moment: https://code.djangoproject.com/ticket/28333 – Brian Destura Jul 09 '21 at 08:02
  • As for other options, raw sql? – Brian Destura Jul 09 '21 at 08:03
  • @bdbd This is one piece in a larger query that I'm trying to build. Ideally I do it all with the ORM rather than raw SQL. I found [this article](https://blog.oyam.dev/django-filter-by-window-function/) which shows how to embed the query from a query set as a subquery to filter on it. Then I also found `Subquery()` in the Django API. But I have not yet figured out how to use it instead of a raw SQL string as shown in the blog article. – Code-Apprentice Jul 09 '21 at 19:36
  • 1
    [This comment](https://code.djangoproject.com/ticket/28333#comment:20) on @BrianDestura's post might be useful (TLDR: use a `RawQuerySet` to wrap your subquery with a condition) – Agustín Jul 11 '22 at 13:58

1 Answers1

5

Django 4.2 supports filtering on WINDOW expressions. See the release notes and also the pull request.

jnns
  • 5,148
  • 4
  • 47
  • 74
  • Thanks for the information! This is an old question and I've moved on from the project that originally prompted me to ask. I don't even do Django currently. – Code-Apprentice Feb 24 '23 at 16:21