RIGHT OUTER JOIN in SQLAlchemy

Question

I have two tables beard and moustache defined below:

+--------+---------+------------+-------------+
| person | beardID | beardStyle | beardLength |
+--------+---------+------------+-------------+

+--------+-------------+----------------+
| person | moustacheID | moustacheStyle |
+--------+-------------+----------------+

I have created a SQL Query in PostgreSQL which will combine these two tables and generate 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 can not create SQLAlchemy representation of it. I tried several ways from implementing from_statement to outerjoin but none of them really worked. Can anyone help me with it?

Why would you want the result to be represented in this format rather than a `Person` class and two relationships `Person.beards` and `Person.mustashes`? Basically my question is: why use `sqlalchemy` to do something so `SQL`? If you have relationships defined, you could just issue `session.query(Person).options(joinedload('beards')).options(joinedload('mustashes')).all()`, and the query generated will be very similar, but the result would still be an instance of `Person` and not a `tuple`. — van, Jul 09 '12 at 21:54
Table mentioned above is a pseudo table. I'm looking to create Activity List like Facebook News Feed where all types of data is there from Likes to Comments etc. Assuming they have different table for each of such objects in order to create Facebook News feed there has to be `JOIN` which will combine all of them to create a single list. So that I can apply operations like `ORDER BY` on that list — Nihar Sawant, Jul 10 '12 at 07:09

Tim · Answer 1 · 2019-12-04T20:24:42.913

14

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".

edited Dec 04 '19 at 20:24

answered Jan 24 '19 at 15:27

Tim

12,318
7
50
72

I forgot, and I believe someone might also miss it: adding filter/where `B.id.is_(None)` to work as expected. – Uriel Alves Aug 31 '22 at 12:52

score 5 · Accepted Answer · edited May 23 '17 at 12:00

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.

It's certainly not a hack. I use similar logic frequently, using sqlalchemy and plain sql. Not all databases offer outer joins. Netezza only offers 'left join', 'right join', and 'join'. 'join' being equal to 'inner join'. If you want any outer join from Netezza you would do something similar to this. Just saying. — braintho, Feb 12 '20 at 21:08

score 3 · Answer 3 · answered May 18 '20 at 04:30

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 ...

class User(Base):
    __tablenane = "user"

    id = Column(Integer, primary_key=True)
    group_id = Column(Integer, ForeignKey("group.id"))

class Group(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).

score 1 · Answer 4 · answered Feb 08 '15 at 13:32

1

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

answered Feb 08 '15 at 13:32

Eric

95,302
53
242
374

When should this style (`select`) be used instead of the "normal" way (`session.query`) ? – Matthew Moisen Sep 11 '16 at 04:15
@MatthewMoisen: as far I know, you can't put a `.query` inside a from clause. – Eric Sep 11 '16 at 04:34

score 1 · Answer 5 · answered May 08 '20 at 01:13

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 RIGHT OUTER JOIN B ON A.common = B.common;
SELECT * FROM B LEFT OUTER JOIN 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 result of A RIGHT JOIN B in a list of object A
session.query(A).rightouterjoin(B).all()   # SELECT A.* FROM A RIGHT OUTER JOIN 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 LEFT OUTER JOIN 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 LEFT OUTER JOIN 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.

RIGHT OUTER JOIN in SQLAlchemy

5 Answers5

Linked