How to filter a Window function query?

Using Django
1

I would like to filter a QuerySet which uses a Window function to only returns the entries where to row number is <= 10. It is not supported by the ORM to filter directly on this query set (https://code.djangoproject.com/ticket/28333). Normally this could be achieved by wrapping the query inside a new SELECT statement and filtering in there. Is there a work around available for this issue?

For example:

    row_number = Window(
        expression=RowNumber(),
        output_field='row',
        partition_by=(TruncDate('created')),
        order_by=F('created').desc())

    posts_with_row = (Post.objects
             .annotate(row=row_number)
             .filter(rowr__range=(1, 10)))
2

I think the ticket is alluding to using SubQuery to use the windowed queryset as an inner query: https://docs.djangoproject.com/en/3.0/ref/models/expressions/#subquery-expressions . Not sure of the syntax how to do that right now though.

3

Thank you, that looks like the right direction. Do you have a idea how I could then filter the row_number outside the SubQuery?

1 Like