Django filtering on Window functions

user_2339308 0
Viewed 2538

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?

window-functions django-models django
2 Answers

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')
)
user_17115770

I haven't tested this but using the new Window functions mentioned in the comment by @Bobort you could try something like this, the SQL it produces is pretty close to what you want apart from the "-1"

from django.db.models import F
from django.db.models.expressions import Window
from django.db.models.functions import Rank

B.objects.annotate(rank=Window(
    expression=Rank(),
    order_by=F('order_index').desc(),
    partition_by=[F('a_id')]))
user_21677490
Related