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?

Saltire

This is known issue as of the moment: code.djangoproject.com/ticket/28333 – Martens

As for other options, raw sql? – Martens

@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 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. – Saltire

This comment on @BrianDestura's post might be useful (TLDR: use a RawQuerySet to wrap your subquery with a condition) – Tater

Answer 1

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

Shark

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. – Saltire