Right Outer Join In Sqlalchemy

I have two tables beard and moustache defined below: +--------+---------+------------+-------------+ | person | beardID | beardStyle | beardLength | +--------+---------+-----------

Solution 1:

In SQL, A RIGHT OUTER JOIN B is equivalent of B LEFT OUTER JOIN A. So, technically there is no need in the RIGHT OUTER JOIN API - it is possible to do the same by switching the places of the target "selectable" and joined "selectable". SQL Alchemy provides an API for this:

# 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() doc, section "Controlling what to Join From".

Solution 2:

From @Francis P's suggestion 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 can't call it as an answer because it appears as a hack. This is one more reason why there should be RIGHT OUTER JOIN in sqlalchemy.

Solution 3:

If A,B are tables, you can achieve: SELECT * FROM A RIGHT JOIN B ON A.id = B.a_id WHERE B.id = my_id by: SELECT A.* FROM B JOIN ON A.id = B.a_id WHERE B.id = my_id in sqlalchemy:

from sqlalchemy import select


result = session.query(A).select_entity_from(select([B]))\
    .join(A, A.id == B.a_id)\
    .filter(B.id == my_id).first()

for example:

# import ...classUser(Base):
    __tablenane = "user"id = Column(Integer, primary_key=True)
    group_id = Column(Integer, ForeignKey("group.id"))

classGroup(Base):
    __tablename = "group"id = Column(Integer, primary_key=True)
    name = Column(String(100))

You can get user group name by user id with the follow code:

# import ...from sqlalchemy import select

user_group_name, = session.query(Group.name)\
    .select_entity_from(select([User]))\
    .join(Group, User.group_id == Group.id)\
    .filter(User.id == 1).first()

If you want a outer join, use outerjoin() instead of join().

This answer is a complement to the previous one(Timur's answer).

Solution 4:

Here's what I've got, 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)

Which seems to work on it's own, but seems to be impossible to join with another select expression

Solution 5:

Unfortunately, SQLAlchemy only provides API for LEFT OUTER JOIN as .outerjoin(). As mentioned above, we could get a RIGHT OUTER JOIN by reversing the operands of LEFT OUTER JOIN; eg. A RIGHT JOIN B is the same as B LEFT JOIN A. In SQL, the following statements are equivalent:

SELECT*FROM A RIGHTOUTERJOIN B ON A.common = B.common;
SELECT*FROM B LEFTOUTERJOIN A ON A.common = B.common;

However, in SQLAlchemy, we need to query on a class then perform join. The tricky part is rewriting the SQLAlchemy statement to reverse the tables. For example, the results of the first two queries below are different as they return different objects.

# No such API (rightouterjoin()) but this is what we want.
# This should return the resultof A RIGHTJOIN B in a list of object A
session.query(A).rightouterjoin(B).all()   # SELECT A.*FROM A RIGHTOUTERJOIN B ...

# We could reverse A and B but this returns a list of object B
session.query(B).outerjoin(A).all()        # SELECT B.*FROM B LEFTOUTERJOIN A ...

# This returns a list of object A by choosing the 'left' side to be B using select_from()
session.query(A).select_from(B).outerjoin(A).all()   # SELECT A.*FROM B LEFTOUTERJOIN A ...

# For OP's example, assuming we want to return a list of beard object:
session.query(beard).select_from(moustache).outerjoin(beard).all()

Just adding to the answers, you can find the use of select_from from the SQLAlchemy doc.