8.38. SQLAlchemy Query Join

Important

Join
Select From
Multiple Joins
Outer Join
Reset Join Point

✘>>> q = session.query(User).\
...         join(User.addresses).\
...         filter(Address.email_address.like('%@aol.com')).\
...         options(contains_eager(User.addresses)).\
...         populate_existing()

8.38.1. Join

https://docs.sqlalchemy.org/en/stable/orm/query.html#sqlalchemy.orm.Query.join

✘>>> q = session.query(User).join(User.addresses)

SELECT user.id, user.name
FROM user JOIN address ON user.id = address.user_id

8.38.2. Select From

https://docs.sqlalchemy.org/en/stable/orm/query.html#sqlalchemy.orm.Query.select_from

8.38.3. Multiple Joins

the order in which each call to the join() method occurs is important.

✘>>> q = (
...     select(User).
...     join(User.orders).
...     join(Order.items).
...     join(Item.keywords))

8.38.4. Outer Join

https://docs.sqlalchemy.org/en/stable/orm/query.html#sqlalchemy.orm.Query.outerjoin

8.38.5. Reset Join Point

https://docs.sqlalchemy.org/en/stable/orm/query.html#sqlalchemy.orm.Query.reset_joinpoint

8.38.6. Use Case - 1

✘>>> q = session.query(User).join(Address, User.id==Address.user_id)
>>> q = session.query(User).join(Address, User.addresses)

8.38.7. Use Case - 2

✘>>> a1 = aliased(Address)
>>> a2 = aliased(Address)
>>>
>>> q = session.query(User).\
...         join(a1, User.addresses).\
...         join(a2, User.addresses).\
...         filter(a1.email_address=='ed@foo.com').\
...         filter(a2.email_address=='ed@bar.com')

8.38.8. Use Case - 3

✘>>> a1 = aliased(Address)
>>> a2 = aliased(Address)
>>>
>>> q = session.query(User).\
...         join(User.addresses.of_type(a1)).\
...         join(User.addresses.of_type(a2)).\
...         filter(a1.email_address == 'ed@foo.com').\
...         filter(a2.email_address == 'ed@bar.com')

8.38.9. Use Case - 4

✘>>> q = session.query(User).join(
...     User.addresses.and_(Address.email_address != 'foo@bar.com')
... )

8.38.10. Use Case - 5

✘>>> subq = session.query(Address).\
...     filter(Address.email_address == 'ed@foo.com').\
...     subquery()
>>>
>>> q = session.query(User).join(
...     subq, User.id == subq.c.user_id
... )

8.38.11. Further Reading