Add support for annotate after union

[Alexandru Chirila]

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")
)

[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:

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():
        ...

[Simon Charette]

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).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.