Using UNION in django ORM syntax between different classes in the same hierarchy

Question

I need to implement something like

 (SELECT table1.*, val=2 FROM table1 INNER JOIN table2 ON table1.id = table2.id WHERE some_condition)
 UNION
 (SELECT table1.*, val=3 FROM table1 INNER JOIN table3 ON table1.id = table3.id WHERE some_condition)

or

 (SELECT val1, val2, val3, val=2 FROM table2 WHERE some_condition)
 UNION
 (SELECT val1, val2, val3, val=3 FROM table3 WHERE some_condition)

I.e. having the class 'table1', 'table2' and 'table3' where table2 and table3 are derived from table1 I need to select all of them with additional field. The problem is that I'd prefere to avoid using raw sql queries as some_condition should be reusable. If I try to use extra it complains that I use .extra.

Answer 1

You'll spend a lot of time fighting the django ORM API if you attempt to write this JOIN/UNION outside of a .execute call. If this is going to be very common, consider using a VIEW and then just do a simple SELECT from your newly created VIEW.

Answer 2

You could rewrite it as a subquery:

select val1, val2, val3, val4
from (
     SELECT val1, val2, val3, val=2 as val4 FROM table2
     UNION
     SELECT val1, val2, val3, val=3 as val4 FROM table3
     ) t
where some_condition

Be wary however, because it's not necessarily the best thing to do.

It might satisfying because you avoided to rewrite the condition, but for the query planner, the two queries might be very different animals. Postgres is occasionally smart enough to inject where conditions into sub-statements, but one case I've never seen it do so is when a sub-statement has any kind of aggregates.

Specifically, if the condition is placed outside (as in the above), you'll start by appending two entire tables. You'll then aggregate them in order to eliminate duplicates (this is a step which you'll avoid by using union all instead of union, btw), and you'll finally filter rows in the resulting set match your condition.

By contrast, when placed inside the individual bits, you'll append and sort two smaller sets of rows. It'll be much faster and consume much less memory.

In short, filter rows as early as you can in your queries.

Answer 3

I decided to port to sqlalchemy - my problem fit badly into django and I'm happier with tg2 (that said - it does not mean that django is bad - it is just not well suited for my task).