I'll be using the mappings laid out in
http://docs.sqlalchemy.org/en/latest/orm/inheritance.html for my examples.
"Employee" is configured with "with_polymorphic": "*"
So if I were to do a query like so:
db.query(Company).filter(Company.id == 1).options(subqueryload('employee'))
The eager load generates the following SQL.
SELECT employee.id ...
FROM (
SELECT company.id AS company_id
FROM company
WHERE company.id = 1
) AS anon_1
JOIN (
employee
LEFT OUTER JOIN engineer ON employee.id = engineer.id
LEFT OUTER JOIN manager ON employee.id = manager.id
) ON anon_1.company_id = employee.company_id ORDER BY anon_1.company_id
This loads all the necessary information to construct Engineer and Manager
objects, which is what I want.
Now lets suppose the Engineer has a "machines" relationship, which I'd like
to eagerload. Basically, I'd like to do this:
db.query(Company).filter(Company.id == 1).options(subqueryload('employee'),
subqueryload('employee.machines'))
Of course, this doesn't actually work, because Employee does not have
"machines" attribute, Engineer does. So I use the technique laid out
here:
http://docs.sqlalchemy.org/en/latest/orm/inheritance_loading.html#eager-loading-of-specific-or-polymorphic-subtypes
db.query(Company).filter(Company.id == 1).options(
subqueryload('employees'),
subqueryload(Company.employees.of_type(Engineer)).subqueryload(Engineer.
machines)
)
However, the second subqueryload seems to override the first, so only
Engineer is join loaded and managers are now lazy loaded. Here's roughly
what the SQL looks like:
--- Does not load manager properties
SELECT employee.id ...
FROM (
SELECT company.id AS company_id
FROM company
WHERE company.id = 1
) AS anon_1
JOIN (
employee
LEFT OUTER JOIN engineer ON employee.id = engineer.id
--- "manager" table is not joined
) ON anon_1.company_id = employee.company_id ORDER BY anon_1.company_id
--- This means that every time we access a manager object, something like
this is executed:
SELECT ...
FROM manager
WHERE id = 1
Is there a way I can eager load relationships on subtypes without giving up
the joined load?
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
