sql alchemy many to many join code example

Example 1: sqlalchemy query join many to many

session.query(User).join((Group, User.groups)) \
    .join((Department, Group.departments)).filter(Department.name == 'R&D')

Example 2: sql alchemy query table and include relationship

# joined-load the "orders" collection on "User"
query(User).options(joinedload(User.orders))

# joined-load Order.items and then Item.keywords
query(Order).options(
    joinedload(Order.items).joinedload(Item.keywords))

# lazily load Order.items, but when Items are loaded,
# joined-load the keywords collection
query(Order).options(
    lazyload(Order.items).joinedload(Item.keywords))