Opened 9 months ago

Closed 9 months ago

Last modified 9 months ago

#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 Alexandru Chirila, 9 months ago

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

comment:2 by Simon Charette, 9 months ago

Resolution: duplicate
Status: newclosed

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.

Last edited 9 months ago by Simon Charette (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top