From f387d024fc75569d2a4a338bfda76cc2f328f627 Mon Sep 17 00:00:00 2001 From: Simon Charette Date: Wed, 10 Aug 2022 08:22:01 -0400 Subject: Refs #28333 -- Added partial support for filtering against window functions. Adds support for joint predicates against window annotations through subquery wrapping while maintaining errors for disjointed filter attempts. The "qualify" wording was used to refer to predicates against window annotations as it's the name of a specialized Snowflake extension to SQL that is to window functions what HAVING is to aggregates. While not complete the implementation should cover most of the common use cases for filtering against window functions without requiring the complex subquery pushdown and predicate re-aliasing machinery to deal with disjointed predicates against columns, aggregates, and window functions. A complete disjointed filtering implementation should likely be deferred until proper QUALIFY support lands or the ORM gains a proper subquery pushdown interface. --- docs/ref/models/expressions.txt | 31 +++++++++++++++++++++++++------ docs/releases/4.2.txt | 4 +++- 2 files changed, 28 insertions(+), 7 deletions(-) (limited to 'docs') diff --git a/docs/ref/models/expressions.txt b/docs/ref/models/expressions.txt index 25edd1f3e8..95f093e2a3 100644 --- a/docs/ref/models/expressions.txt +++ b/docs/ref/models/expressions.txt @@ -741,12 +741,6 @@ instead they are part of the selected columns. .. class:: Window(expression, partition_by=None, order_by=None, frame=None, output_field=None) - .. attribute:: filterable - - Defaults to ``False``. The SQL standard disallows referencing window - functions in the ``WHERE`` clause and Django raises an exception when - constructing a ``QuerySet`` that would do that. - .. attribute:: template Defaults to ``%(expression)s OVER (%(window)s)'``. If only the @@ -819,6 +813,31 @@ to reduce repetition:: >>> ), >>> ) +Filtering against window functions is supported as long as lookups are not +disjunctive (not using ``OR`` or ``XOR`` as a connector) and against a queryset +performing aggregation. + +For example, a query that relies on aggregation and has an ``OR``-ed filter +against a window function and a field is not supported. Applying combined +predicates post-aggregation could cause rows that would normally be excluded +from groups to be included:: + + >>> qs = Movie.objects.annotate( + >>> category_rank=Window( + >>> Rank(), partition_by='category', order_by='-rating' + >>> ), + >>> scenes_count=Count('actors'), + >>> ).filter( + >>> Q(category_rank__lte=3) | Q(title__contains='Batman') + >>> ) + >>> list(qs) + NotImplementedError: Heterogeneous disjunctive predicates against window functions + are not implemented when performing conditional aggregation. + +.. versionchanged:: 4.2 + + Support for filtering against window functions was added. + Among Django's built-in database backends, MySQL 8.0.2+, PostgreSQL, and Oracle support window expressions. Support for different window expression features varies among the different databases. For example, the options in diff --git a/docs/releases/4.2.txt b/docs/releases/4.2.txt index cb64750009..ed00ee1350 100644 --- a/docs/releases/4.2.txt +++ b/docs/releases/4.2.txt @@ -189,7 +189,9 @@ Migrations Models ~~~~~~ -* ... +* ``QuerySet`` now extensively supports filtering against + :ref:`window-functions` with the exception of disjunctive filter lookups + against window functions when performing aggregation. Requests and Responses ~~~~~~~~~~~~~~~~~~~~~~ -- cgit v1.3