How to explicitly load relationships on an existing object?

Question

I have a SQLAlchemy model Foo which contains a lazy-loaded relationship bar which points to another model that also has a lazy-loaded relationship foobar.

When querying normally I would use this code to ensure that all objects are loaded with a single query:

session.query(Foo).options(joinedload('bar').joinedload('foobar'))

However, now I have a case where a base class already provides me a Foo instance that was retrieved using session.query(Foo).one(), so the relationships are lazy-loaded (which is the default, and I don't want to change that).

For a single level of nesting I wouldn't mind it being loaded once I access foo.bar, but since I also need to access foo.bar[x].foobar I really prefer to avoid sending queries in a loop (which would happen whenever I access foobar).

I'm looking for a way to make SQLAlchemy load the foo.bar relationship while also using the joinedload strategy for foobar.

Answer 1

I ran into a similar situation recently, and ended up doing the following:

eager_loaded = db.session.query(Bar).options(joinedload('foobar'))
    .filter_by(bar_fk=foo.foo_pk).all()

Assuming you can recreate the bar join condition in the filter_by arguments, all the objects in the collection will be loaded into the identity map, and foo.bar[x].foobar will not need to go to the database.

One caveat: It looks like the identity map may dispose of the loaded entities if they are no longer strongly referenced - thus the assignment to eager_loaded.