[Django] #35146: Add support for annotate after union

[Django]

#35146: Add support for annotate after union
-------------------------------------+-------------------------------------
Reporter: Alexandru | Owner: nobody
Chirila |
Type: New | Status: new
feature |
Component: Database | Version:
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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

{{{#!sql
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

{{{#!python
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")
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35146>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35146: Add support for annotate after union
-------------------------------------+-------------------------------------

Reporter: Alexandru Chirila | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Alexandru Chirila):

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:

{{{#!python
from django.db import connection

query1 = Products1.objects.filter(foo="bar")
query2 = Products2.objects.filter(foo="bar")

union_query = query1.union(query2)

compiler = union_query.query.get_compiler(connection=connection)
sql, params = union_query.query.as_sql(compiler, connection)

with connection.cursor() as cursor:
cursor.execute(
f"SELECT release_date, MIN(price), MAX(price) "
f"FROM ({sql}) AS tmp_union "
f"GROUP BY release_date",
params,
)

for release_date, min_price, max_price in cursor.fetchall():
...
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35146#comment:1>

[Django]

#35146: Add support for annotate after union
-------------------------------------+-------------------------------------
Reporter: Alexandru Chirila | Owner: nobody

Type: New feature | Status: closed

Component: Database layer | Version:
(models, ORM) |

Severity: Normal | Resolution: duplicate

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* status: new => closed
* resolution: => duplicate

Comment:

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

{{{#!python

query1 = Products1.objects.filter(foo="bar")

query2 = Products2.objects.filter(foo="baz")
query1.union(query2).as_subquery("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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35146#comment:2>

[Django]

#35146: Add support for annotate after union
-------------------------------------+-------------------------------------
Reporter: Alexandru Chirila | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Stephen):

* cc: Stephen (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/35146#comment:3>