Opened 4 years ago

Last modified 3 weeks 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 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 (14)

comment:1 Changed 4 years ago by Mads Jensen

Description: modified (diff)
Triage Stage: UnreviewedAccepted

comment:2 Changed 2 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 QueryWrapper would simply make an inner query of the QuerySet to date so that subsequent operations act upon it as if it were a table.

Last edited 19 months ago by Bernd Wechner (previous) (diff)

comment:3 Changed 19 months ago by Alexandr Artemyev

Cc: Alexandr Artemyev added

comment:4 Changed 17 months ago by Andy Terra

Cc: Andy Terra added

comment:5 Changed 14 months ago by Étienne Beaulé

Cc: Étienne Beaulé added

comment:6 Changed 8 months ago by Mariusz Felisiak

Keywords: GSoC added

comment:7 Changed 8 months ago by Manav Agarwal

Owner: changed from nobody to Manav Agarwal
Status: newassigned

comment:9 Changed 7 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 7 months ago by Manav Agarwal (previous) (diff)

comment:10 Changed 6 months ago by Michael Wheeler

Cc: Michael Wheeler added

comment:11 Changed 4 months ago by Manav Agarwal

Owner: Manav Agarwal deleted

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

Cc: şuayip üzülmez added

comment:13 Changed 2 months ago by John Speno

Cc: John Speno added

comment:14 Changed 4 weeks 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 3 weeks ago by Mariusz Felisiak

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