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 )
#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
Description: | modified (diff) |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:3 Changed 2 years ago by
Cc: | Alexandr Artemyev added |
---|
comment:4 Changed 2 years ago by
Cc: | Andy Terra added |
---|
comment:5 Changed 2 years ago by
Cc: | Étienne Beaulé added |
---|
comment:6 Changed 18 months ago by
Keywords: | GSoC added |
---|
comment:7 Changed 17 months ago by
Owner: | changed from nobody to Manav Agarwal |
---|---|
Status: | new → assigned |
comment:9 Changed 17 months ago by
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)
- A separate QueryWrapper Class which will have syntax like this
Window.objects.annotate(row=QueryWrapper(Window(expression=RowNumber())).filter(row__gt=1)
OR- QueryWrapper class internally implemented for window function to automatically generate SQL subquery for all window expressions.
- Use the subquery class internally to make execute all window expression related queries as subqueries.
- 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.
comment:10 Changed 16 months ago by
Cc: | Michael Wheeler added |
---|
comment:11 Changed 14 months ago by
Owner: | Manav Agarwal deleted |
---|
comment:12 Changed 14 months ago by
Cc: | şuayip üzülmez added |
---|
comment:13 Changed 12 months ago by
Cc: | John Speno added |
---|
comment:14 Changed 11 months ago by
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
Status: | assigned → new |
---|
comment:16 Changed 10 months ago by
Cc: | Ad Timmering added |
---|
comment:17 Changed 8 months ago by
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
Cc: | Hannes Ljungberg added |
---|
comment:19 Changed 5 months ago by
Cc: | Dave Johansen added |
---|
comment:20 Changed 5 months ago by
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.
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.