On Jul 31, 2010, at 6:56 PM, Nikolaj wrote: > I have a one to one relationship between Company and Employee where > Employee is subclassed using joined table inheritance into Manager and > Engineer. I'm trying to query for a list of companies with the > employee eagerloaded including the type-specific columns. > > I've been looking at the example at > http://www.sqlalchemy.org/docs/mappers.html#creating-joins-to-specific-subtypes, > but I can't figure out how to construct a query to eagerload the > attributes onto the employee on the company. > > Here's what I have at the moment: > > companies = Session.query(Company) \ > .join(( > Employee.__table__ \ > .outerjoin(Engineer.__table__) \ > .outerjoin(Manager.__table__), > Company.employee > )) \ > .all() > > which generates > > SELECT <company columns> > FROM companies INNER JOIN ( > SELECT <employee columns>, <engineer columns>, <manager columns> > FROM employees > LEFT OUTER JOIN engineers ON employees.id = engineers.id > LEFT OUTER JOIN managers ON employees.id = managers.id > ) AS anon_1 ON anon_1.employees_id = companies.employee_id > > So I guess I need to get the Engineer and Manager columns into the > field list and then for them to be populated on the Company.employee > relationship - I'm struggling with this. Is there such as thing as > with_polymorphic() for joinedload()?
there's not at the moment a with_polymorphic for joinedload(). Your join above is close but the subquery part is where it goes wrong. Just inner join to employees, outer join to engineer and manager, and then use contains_eager() for employees. > > My next question is about how to manage this sort of query strategy in > the long term when the total number of subtypes might get larger (e.g. > 50 types of employees). The number of types per company would remain > low (e.g. a TechCompany might have Engineers and Managers, but not > Designers or Salesmen). Should I somehow store the types of employee > to query polymorphically for on a per-Company basis? What you need to consider here is that an arbitrarily large list of join targets is going to perform very poorly, especially if the relationship you're working with is one to many, as is the case here. If its a one to many you'd be better off with "subqueryload" anyway. Assuming subqueryload works, you might try using its former recipe version at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading to customize the tables pulled into the collection load, or maybe even something that loads in groups of collections based on subcategories of Company objects. Sorry we don't have a better option here. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
