ROTATING VERSION IN SQLAlchemy

Question

I have two beard and moustache tables defined below:

 +--------+---------+------------+-------------+ | person | beardID | beardStyle | beardLength | +--------+---------+------------+-------------+ +--------+-------------+----------------+ | person | moustacheID | moustacheStyle | +--------+-------------+----------------+ 

I created SQL Query in PostgreSQL, which joins these two tables and generates the following result:

 +--------+---------+------------+-------------+-------------+----------------+ | person | beardID | beardStyle | beardLength | moustacheID | moustacheStyle | +--------+---------+------------+-------------+-------------+----------------+ | bob | 1 | rasputin | 1 | | | +--------+---------+------------+-------------+-------------+----------------+ | bob | 2 | samson | 12 | | | +--------+---------+------------+-------------+-------------+----------------+ | bob | | | | 1 | fu manchu | +--------+---------+------------+-------------+-------------+----------------+ 

Query:

 SELECT * FROM beards LEFT OUTER JOIN mustaches ON (false) WHERE person = "bob" UNION ALL SELECT * FROM beards b RIGHT OUTER JOIN mustaches ON (false) WHERE person = "bob" 

However, I cannot create a SQLAlchemy view. I tried several ways from implementing from_statement to outerjoin , but none of them worked. Can anyone help me with this?

Answer 1

From the @Francis P sentence I came up with this snippet:

 q1 = session.\ query(beard.person.label('person'), beard.beardID.label('beardID'), beard.beardStyle.label('beardStyle'), sqlalchemy.sql.null().label('moustachID'), sqlalchemy.sql.null().label('moustachStyle'), ).\ filter(beard.person == 'bob') q2 = session.\ query(moustache.person.label('person'), sqlalchemy.sql.null().label('beardID'), sqlalchemy.sql.null().label('beardStyle'), moustache.moustachID, moustache.moustachStyle, ).\ filter(moustache.person == 'bob') result = q1.union(q2).all() 

However, this works, but you cannot call it an answer because it looks like a hack. This is another reason why sqlalchemy should have a RIGHT OUTER JOIN .

Answer 2

In SQL, you can replace A RIGHT OUTER JOIN B with B LEFT OUTER JOIN A to get the same result. Thus, you do not need to explicitly use the RIGHT OUTER JOIN in the API, but you can do the same by switching selectable and join selectable destinations. SQL Alchemy provides an API for this:

I.e:

 # this **fictional** API: query(A).join(B, right_outer_join=True) # right_outer_join doesn't exist in SQLA! # can be implemented in SQLA like this: query(A).select_entity_from(B).join(A, isouter=True) 

See SQLA Query.join () , "Controlling What to Join."

Answer 3

Here is what I have, the ORM style:

 from sqlalchemy.sql import select, false stmt = ( select([Beard, Moustache]) .select_from( outerjoin(Beard, Moustache, false()) ).apply_labels() ).union_all( select([Beard, Moustache]) .select_from( outerjoin(Moustache, Beard, false()) ).apply_labels() ) session.query(Beard, Moustache).select_entity_from(stmt) 

It seems to work on its own, but it seems impossible to join another select statement

Answer 4

Why not use this simple query:

 SELECT person, beardID, beardStyle, beardLength, NULL AS moustacheID, NULL AS moustacheStyle FROM beards WHERE person = "bob" UNION SELECT person, NULL AS beardID, NULL AS beardStyle, NULL AS beardLength, moustacheId, moustacheStyle FROM mustaches WHERE person = "bob"