CODEWITHSUNDEEP
pythonjoinsqlalchemy
benvc
benvc

Reputation: 15120

sqlalchemy joinedload: syntax to load multiple relationships more than 1 degree separated from query table?

Is there a more concise syntax for using sqlalchemy joinedload to eager load items from more than one table that only relates to the query table by way of another intermediate table (or is there an alternative load syntax that is better for what I am trying to do)?

For example, using the familiar data structure of questions, answers, etc, is there a more succinct way than the example below to query a question, eager load the related answers, and eager load both the answer comments and the answer votes (assume, for this example, that both of these answer related items are contained in separate tables)? 

from sqlalchemy.orm import joinedload

result = session.query(Question).\
    options(
       joinedload(Question.answers).
       joinedload(Answer.comments)
       ).\
    options(
       joinedload(Question.answers).
       joinedload(Answer.votes)
       ).\
    filter(Question.id == '1').\
    first()

I could not find any examples of loading multiple tables by way of an intermediate table at Relationship Loading Techniques (or anywhere else for that matter). I did attempt to include multiple sub-relationships inside a single joinedload like...

result = session.query(Question).\
    options(joinedload(Question.answers, Answer.comments, Answer.votes)).\
    filter(Question.id == '1').\
    first()

...but that approach, not surprisingly, just chains the joins.

Upvotes: 13

Views: 7291

Answers (1)

Christophe Biocca
Christophe Biocca

Reputation: 3468

You want Load.options()

Then your code would be something like:

result = session.query(Question).\
    options(
        joinedload(Question.answers).\
        options(
            joinedload(Answer.comments),
            joinedload(Answer.votes)
            )
        ).\
    filter(Question.id == '1').\
    first()

Upvotes: 19

Related Questions