#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):
Submit [https://github.com/django/django/pull/15922 a PR] that adds
support for jointed predicates but still disallowed disjointed ones.
For example, given the following model and queryset
{{{#!python
class Employee(models.Model):
name = models.CharField(max_length=50)
department = models.CharField(max_length=50)
salary = models.IntegerField()
class PastEmployeeDepartment(models.Model):
employee = models.ForeignKey(Employee,
related_name="past_departments")
department = models.CharField(max_length=50)
queryset = Employee.objects.annotate(
dept_max_salary=Window(Max(), partition_by="department"),
dept_salary_rank=Window(Rank(), partition_by="department",
order_by="-salary"),
past_depths_cnt=Count("past_departments"),
)
}}}
All of the following is supported
{{{#!python
# window predicate will be pushed to outer query
queryset.filter(dept_max_salary__gte=F("salary"))
SELECT * FROM (...) "quantify" WHERE dept_max_salary >=
"quantify"."salary"
# department predicate will be applied in inner query
queryset.filter(department="IT", dept_max_salary__gte=F("salary"))
SELECT * FROM (... WHERE "department" = 'IT') "quantify" WHERE
dept_max_salary >= "quantify"."salary"
# aggregate predicate will be applied in the inner query
queryset.filter(past_depths_cnt__gte=1, dept_max_salary__gte=F("salary"))
SELECT * FROM (... HAVING COUNT("pastemployeedepartment"."id" >= 1)
"quantify" WHERE dept_max_salary >= "quantify"."salary"
}}}
Some form of disjointed predicates against window functions (using `OR`)
are also supported as long as they are ''only'' against window functions
{{{#!python
# Disjointed predicates only about window functions is supported
queryset.filter(Q(dept_max_salary__gte=F("salary")) |
Q(dept_salary_rank__lte=2))
SELECT * FROM (...) "quantify" WHERE "dept_max_salary" >=
"quantify"."salary" OR "dept_salary_rank" <= 2
}}}
And limits are only applied on the outer query, once all window function
filters are applied.
The following is not supported
1. Disjointed filters mixing predicates against window functions and
aggregates and/or column references as it's really hard to emulate without
getting in multiple level of subquery pushdown particularly if aggregation
is involved.
2. Filtering against columns masked by the usage of `values`,
`values_list`, or `alias`. This one could be to solved by adding another
layer of subquery pushdown that avoids applying the mask in the subquery
but does so in an outermost query over the one used for window filtering.
3. Passing window functions instances directly to `filter` and `exclude`
instead of referencing annotated window functions.
Feedback about the proposed supported feature set and implementation is
very welcome.
--
Ticket URL: <https://code.djangoproject.com/ticket/28333#comment:24>
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/010701827a6f1b47-ac269a1d-bde5-4635-be21-0624228d5f19-000000%40eu-central-1.amazonses.com.
