Django filtering on Window functions

Question

I have two Models in Django, A and B.

Each A has several Bs assigned to it, and the Bs are ordered, which is done with a field B.order_index that counts upwards from zero for any A.

I want to write a query that checks if there is any A where some of the Bs have a gap or have duplicate order_index values.

In SQL, this could be done like this:

SELECT order_index, RANK() OVER(PARTITION BY a_id ORDER BY order_index ASC) - 1 AS rnk
WHERE rnk = order_index'

However, when I try this in Django with this code:

B.objects.annotate(rank=RawSQL("RANK() OVER(PARTITION BY a_id ORDER BY order_index ASC) - 1", [])).filter(rank=F('order_index'))

I get an error saying:

django.db.utils.ProgrammingError: window functions are not allowed in WHERE
LINE 1: ...- 1) AS "rank" FROM "main_b" WHERE (RANK() OVE...

In SQL this would be easy enough to fix by wrapping the entire thing in a subquery and applying the Where clause to that subquery. How can I do the same in Django?

Answer 1

One possible work-around to express such a query would be to use https://github.com/dimagi/django-cte.

from django_cte import With

cte = With(
    B.objects.all().annotate(
        rank=Window(
            expression=Rank(),
            partition_by=F('a_id'),
            order_by=F('order_index').asc()
        )
    )
)

cte.join(B.objects.all(), id=cte.col.id).with_cte(cte).annotate(
    rank=cte.col.rank
).filter(
    rank=F('order_index')
)