Okay, looks like I found the solution:
all_employee_types = with_polymorphic(Employee, '*')
db.query(Company).filter(Company.id == 1).options(
subqueryload(Company.employees.of_type(all_employee_types)).subqueryload
(all_employee_types.Engineer.machines)
)
On Sunday, January 7, 2018 at 3:18:03 PM UTC-8, Theron Luhn wrote:
>
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
