8.38. SQLAlchemy Query Join

  • 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

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

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

8.38.5. Reset Join Point

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