QUALIFY on a non-window field

18 Views Asked by At

As stated here, from 4.2 Django supports fIltering against a window function.

I've tried it and Django successfully creates a QUALIFY clause when using a window field in the filter clause. However, if using, let's say, a model field, the predicate is assigned to the WHERE clause. Because the WHERE clause is applied before the QUALIFY clause, the window function is applied only to the rows filtered with WHERE.

This is my real problem: I want to get the position of an object in a query. I thought this could be achieved using the window function RowNumber and filtering by the id of the object:

def get_position(qs, obj, order_by=None):
    qs.annotate(
        position=Window(
            expression=RowNumber(), 
            order_by=order_by
        )
    )
    obj = qs.get(id=obj.id)
    # obj.position will always be 1, as WHERE is run before QUALIFY
    return obj.position
0

There are 0 best solutions below