#35146 closed New feature (duplicate)
Add support for annotate after union
Reported by: | Alexandru Chirila | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | |
Severity: | Normal | Keywords: | |
Cc: | Alexandru Chirila | Triage Stage: | Unreviewed |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
This is currently not supported, and the ORM rightly throws an error whenever you attempt to do so. I believe it would be beneficial to have such support allowing a query like this be generated
SELECT release_date AS release_date, MAX(price) AS max_price, MIN(price) AS min_price FROM (SELECT release_date, price FROM products_1 WHERE foo = 'bar' UNION SELECT release_date, price FROM products_2 WHERE foo = 'bar') AS products_subquery GROUP BY release_date
Which might look something like this in the ORM
query1 = Products1.objects.filter(foo="bar") query2 = Products2.objects.filter(foo="bar") query1.union(query2).values("release_date").annotate( min_price=Min("price"), max_price=Max("price") )
Change History (2)
comment:1 by , 9 months ago
comment:2 by , 9 months ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
I think this request is more of a duplicate of #24462.
If it was possible to force a subquery pushdown this could be achieved as
query1 = Products1.objects.filter(foo="bar") query2 = Products2.objects.filter(foo="baz") query1.union(query2).wrap("release_date", "price").values("release_date").annotate( min_price=Min("price"), max_price=Max("price") )
I'm not convinced that having .annotate
perform an implicit subquery pushdown when performed against a composite query is something we should focus on given a subquery pushdown mechanism seem more versatile. In all cases we need such mechanism to exist in the first place which is what #24462 focuses on.
One workaround that exists for this is to build the query yourself and execute it as a raw SQL. However that's not really ideally: