How to union two subqueries in SQLAlchemy and postgresql
i used a little bit different approach:
# the first subquery, select all ids from SOME_TABLE where some_field is not NULLs1 = select([SOME_TABLE.c.id]).where(SOME_TABLE.c.some_field != None)# the second subquery, select all ids from SOME_TABLE where some_field is NULLs2 = select([SOME_TABLE.c.id]).where(SOME_TABLE.c.some_field != None)# union s1 and s2 subqueries together and alias the result as "alias_name"q = s1.union(s2).alias('alias_name')# run the query and limit the aliased result to 10session.query(q).limit(10)Here is the produced sql:
SELECT alias_name.id AS alias_name_id FROM (SELECT some_table.id AS id FROM some_table WHERE some_table.some_field IS NOT NULL UNION SELECT some_table.id AS id FROM some_table WHERE some_table.some_field IS NULL) AS alias_name LIMIT 10I think this is the result you wanted.