#28333: Filter and subquery for window expressions
-------------------------------------+-------------------------------------
Reporter: Mads Jensen | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: window orm filter | Triage Stage: Accepted
subquery GSoC |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
#26780 which is about adding support for slices prefetching (think top-n
results per category) to core would benefit from this feature being
implemented at least partially.
The most difficult part of this issue is not the subquery pushdown itself
(see #24462) but making sure that union filters of the form
`filter(Q(window__lookup=foo) | Q(aggregate__lookup=bar) |
Q(field__lookup=baz))` are resulting in the proper usage of inner query
`WHERE` and `HAVING` and outer query usage of `WHERE` (see the
`Where.split_having`
[https://github.com/django/django/blob/d38324edc840049d42c3454b9487ac370aab5ee9/django/db/models/sql/where.py#L38-L79
method] for the current implementation).
If we were to start by focusing this ticket on the ''simple'' intersection
use cases of the form `filter(window__lookup=foo)` (as reported here and
required by #26780) I suspect we'd cover most of the use cases while
deferring most of the complexity. If someone would like to give this a
shot I'd start by doing the following:
1. Make `Window.filterable = True` for now
2. Adjust `Where.split_having` to properly deal with
`self.contains_over_clause` by returning a triple of the form `(where:
Where, having: Where, window: Where)` and error out when `self.connector
!= AND and self.contains_over_clause`. Possibly rename to
`split_having_window`?
3. Adjust `SQLCompiler.pre_sql_setup` to assign `self.over_where` and use
it in `SQLCompiler.as_sql` to wrap the query in a subquery that `SELECT *
FROM ({subquery_sql}) subquery WHERE {over_where_sql}`
4. Add tests for new supported use cases and disallowed ones.
5. Make `Q.filterable` return `False` when `self.connector != AND and
self.contains_over_clause` but that will result in weird error messages of
the form `Q is disallowed in the filter clause.` so maybe we'll want to
deprecate `Q.filterable` in favour of a `BaseExpression.check_filterable`
method instead that defaults to `raise` the current message and is
overridden in `Q` to raise a proper message with regards to complex
filters window functions.
Happy to review a PR that attempts the above or provide feedback here if
that means this ticket is partially fixed and allows for #26780 to benefit
from this work.
--
Ticket URL: <https://code.djangoproject.com/ticket/28333#comment:21>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070182576ce07c-f9500f6e-d182-4145-bbe8-111703285106-000000%40eu-central-1.amazonses.com.
