How to combine two subqueries in SQLAlchemy and postgresql

Question

Raw SQL required:

SELECT id FROM (SELECT some_table.id FROM some_table WHERE some_table.some_field IS NULL) AS subq1 UNION (SELECT some_table.id WHERE some_table.some_field IS NOT NULL) LIMIT 10; 

Here is the python code:

 import sqlalchemy SOME_TABLE = sqlalchemy.Table( 'some_table', sqlalchemy.MetaData(), sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True), sqlalchemy.Column('some_field', sqlalchemy.Text)) stmt_1 = sqlalchemy.sql.select(SOME_TABLE.columns).where(SOME_TABLE.columns.some_field != None) stmt_2 = sqlalchemy.sql.select(SOME_TABLE.columns).where(SOME_TABLE.columns.some_field == None) # This gets a programming error. stmt_1.union(stmt_2).limit(10); 

Here is the SQL output (with modified parameters) that gets this error: ERROR: syntax error in or next to UNION:

 SELECT some_table.id, some_table.some_field FROM some_table WHERE some_table.some_field IS NOT NULL LIMIT 10 UNION SELECT some_table.id, some_table.some_field FROM some_table WHERE some_table.some_field IS NULL LIMIT 10 LIMIT 10 

How can I alias subqueries?

Answer 1

I used a slightly different approach:

 # the first subquery, select all ids from SOME_TABLE where some_field is not NULL s1 = 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 NULL s2 = 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 10 session.query(q).limit(10) 

Here is the prepared 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 10 

I think this is the result you wanted.