CODEWITHSUNDEEP
sqlalchemy
DivineSlayer
DivineSlayer

Reputation: 339

Proper SqlAlchemy query using contains_eager

I have a query that I have found a working solution for. I am not sure if I am performing the query properly and I was hoping to find out. The tables appear as follows:

owner

The query is:

q = session.query(Person).outerjoin(PetOwner).join(Animal).options(contains_eager("petowner_set"), contains_eager("petowner_set.animal"))

There is a relationship on person connecting it to petowner.

It would be easy if the join from person to petowner AND the join from petowner to animal were both inner joins or both outer joins. However, the join from person to petowner is an outer join and the join from petowner to animal is an inner join. To accomplish this, I added two contains_eager calls to the options.

Is this the correct way to accomplish this?

Upvotes: 6

Views: 11897

Answers (2)

Donald Patterson
Donald Patterson

Reputation: 261

I think the only difference is that you should chain the calls to contains_eager, like this:

q = (session.query(Person)
    .outerjoin(PetOwner)
    .join(Animal)
    .options(
      contains_eager("petowner_set").contains_eager("petowner_set.animal")
    )
)

see: https://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#controlling-loading-via-options

Upvotes: 2

van
van

Reputation: 76962

Short answer: From what I can see, you should be using outerjoin for both, unless you do not want to see Persons who have no animals.

First, Lets take a look at JOINs:

  • both INNER: in this case the result of your query will return only those Persons that have at least one animal (assuming that any PetOwner.animal is not nullable)
  • OUTER for PetOwner, INNER for Animal: same as above (again, assuming that any PetOwner.animal is not nullable)
  • both OUTER: the result of your query will return all Persons irrespective if they own an Animal or not

Then, what do contains_eager do? According to the documentation,

... will indicate to the query that the given attribute should be eagerly loaded from columns currently in the query.

What this means is that when you access the Person.petowner_set, not additional database query will be required, because the SA will will load the relationships from your original query. This has absolutely no impact of how your JOINs work, and only affects the loading of the relationships. This is simply a data loading optimization.

Upvotes: 6

Related Questions