Filtering on a Window function in Django

Question

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?

Answer 1

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