Proper SqlAlchemy query using contains_eager
sqlalchemy
Solution
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 `JOIN`s:
- `both INNER`: in this case the result of your query will return only those `Person`s 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 `Person`s 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.
Problem
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: 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?