Opened 5 years ago

Last modified 5 months ago

#28333 new New feature

Filter and subquery for window expressions

Reported by: Mads Jensen Owned by:
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: window orm filter subquery GSoC
Cc: Alexandr Artemyev, Andy Terra, Étienne Beaulé, Michael Wheeler, şuayip üzülmez, John Speno, Alex Scott, Ad Timmering, Hannes Ljungberg, Dave Johansen Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Mads Jensen)

#26608 will introduce window function expressions, but will disallow filtering on the result of them, e.g.:

Window.objects.annotate(row=Window(expression=RowNumber())).filter(row__gt=1)

is not allowed. Instead, the window function expression should be wrapped in an inner query, and the filtering should be done in an outer query.

Change History (19)

comment:1 Changed 5 years ago by Mads Jensen

Description: modified (diff)
Triage Stage: UnreviewedAccepted

comment:2 Changed 3 years ago by Bernd Wechner

This is 2 years old with no action and I am very keen to see it implemented (need it rather badly).

It strikes me as an aside that a more general approach may kill more birds with one stone. I noticed the rather excellent ExpressionWrapper(), and it struck me that a QueryWrapper() would be a more general solution that covers this particular need and will cover others as well, known and unknown at present.

In short QueryRapper would simply make an inner query of the QuerySet to date so that subsequent operations act upon it as if it were a table.

Version 0, edited 3 years ago by Bernd Wechner (next)

comment:3 Changed 2 years ago by Alexandr Artemyev

Cc: Alexandr Artemyev added

comment:4 Changed 2 years ago by Andy Terra

Cc: Andy Terra added

comment:5 Changed 2 years ago by Étienne Beaulé

Cc: Étienne Beaulé added

comment:6 Changed 18 months ago by Mariusz Felisiak

Keywords: GSoC added

comment:7 Changed 17 months ago by Manav Agarwal

Owner: changed from nobody to Manav Agarwal
Status: newassigned

comment:9 Changed 17 months ago by Manav Agarwal

I was doing some research on this issue and found a few solutions to the problem. (all these are vague ideas. Any suggestions/feedback would be appreciated to make the idea worth implementing)

  1. A separate QueryWrapper Class which will have syntax like this
    1. Window.objects.annotate(row=QueryWrapper(Window(expression=RowNumber())).filter(row__gt=1) OR
    2. QueryWrapper class internally implemented for window function to automatically generate SQL subquery for all window expressions.
  2. Use the subquery class internally to make it execute all window expression related queries as subqueries.
  3. Passing an alias to window expression and then in spite of generating half query with just over and order by clause we may generate a separate select statement when will further be used as a select statement for a separate table in the query.

I personally feel that implementing 1.a would be a good option but as I mentioned above this is just a vague idea and to implement it I need some guidance from someone who is more experienced.

Last edited 17 months ago by Manav Agarwal (previous) (diff)

comment:10 Changed 16 months ago by Michael Wheeler

Cc: Michael Wheeler added

comment:11 Changed 14 months ago by Manav Agarwal

Owner: Manav Agarwal deleted

comment:12 Changed 14 months ago by şuayip üzülmez

Cc: şuayip üzülmez added

comment:13 Changed 12 months ago by John Speno

Cc: John Speno added

comment:14 Changed 11 months ago by Alex Scott

Cc: Alex Scott added

Is there a recommend workaround for not being able to filter on a window function result? Can you wrap it somehow manually?

comment:15 Changed 11 months ago by Mariusz Felisiak

Status: assignednew

comment:16 Changed 10 months ago by Ad Timmering

Cc: Ad Timmering added

comment:17 Changed 8 months ago by rossm6

I have this problem as well.

According to this article https://learnsql.com/blog/window-functions-not-allowed-in-where/ the solution is either to use a common table expression or a subquery which is the FROM clause in the sql query. Neither unfortunately is supported by django it seems. Although I did find this package for the first option - https://docs.djangoproject.com/en/3.2/ref/models/querysets/#extra.

Both of these should be options in the Django ORM right? Each would be a big win for the power of the ORM.

comment:18 Changed 7 months ago by Hannes Ljungberg

Cc: Hannes Ljungberg added

comment:19 Changed 5 months ago by Dave Johansen

Cc: Dave Johansen added

comment:20 Changed 5 months ago by Safa Alfulaij

What I'm doing currently is this hack:

Before:

queryset = MyModel.objects.annotate(row=Window(expression=RowNumber())).filter(row__gt=1)

After:

queryset = MyModel.objects.annotate(row=Window(expression=RowNumber()))
sql, params = queryset.query.sql_with_params()
queryset = queryset.raw(f"SELECT * FROM ({sql}) AS full WHERE row >= 1", params)

I don't see that it's bad to have this currently, with whatever limitations of raw documented in the Window filtering section, then add in more features if real use cases are provided.

Note: See TracTickets for help on using tickets.
Back to Top