CODEWITHSUNDEEP
pythonsqlsqlalchemy
Iorrrrrrrrrr
Iorrrrrrrrrr

Reputation: 422

SQLAlchemy difference between contains_eager and joinedload

what is the difference in SQLAlchemy between contains_eager and joinedload. I read the manual about contains_eager, and manual about joinedload too. They are both can be used for loading one-to-many related rows, or many-to-one.

they are generating the same sql:

query = session.query(User).\
    outerjoin(adalias, User.addresses).\
    options(contains_eager(User.addresses, alias=adalias)).all()

...

SELECT
    users.user_id AS users_user_id,
    users.user_name AS users_user_name,
    adalias.address_id AS adalias_address_id,
    adalias.user_id AS adalias_user_id,
    adalias.email_address AS adalias_email_address,
    (...other columns...)
FROM users
LEFT OUTER JOIN email_addresses AS email_addresses_1
ON users.user_id = email_addresses_1.user_id


>>> jack = session.query(User).\
... options(joinedload(User.addresses)).\
... filter_by(name='jack').all()

SELECT
    addresses_1.id AS addresses_1_id,
    addresses_1.email_address AS addresses_1_email_address,
    addresses_1.user_id AS addresses_1_user_id,
    users.id AS users_id, users.name AS users_name,
    users.fullname AS users_fullname,
    users.password AS users_password
FROM users
LEFT OUTER JOIN addresses AS addresses_1
    ON users.id = addresses_1.user_id
WHERE users.name = ?
['jack']

Can anybody show more particular code examples?

Upvotes: 9

Views: 9888

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

Don't forget many to many and one to one. The difference is that with contains_eager() you instruct SQLA about an existing join or joins that should be used to populate a relationship. This way you can also populate using a filtered subset.

joinedload() on the other hand goes to great lengths to make the joins required for populating entirely transparent and they should not affect the outcome of the original query, as is explained in The Zen of Joined Eager Loading. In other words you cannot use the joined relations for filtering etc.

Upvotes: 11

Related Questions