Thanks for the feedback it ended up being really helpful. I think we are going to try a dev branch using joined table inheritance and at least do some testing, because at this point there are only three or four subtypes that we currently know about.
Also in our new schema the data will be more normalized, so we may end up doing more joins, but in our app we will probably be selecting about a 1/3 the number of rows. Michael Bayer wrote: > 100K rows is not that much. the number of joins you are building into > your schema however will be cumbersome, both from a performance standpoint > as well as a day-to-day working with the database point of view (i.e., > selecting rows from the SQL shell, writing non-ORM scripts, etc.) I tend > towards joined table inheritance when there are going to be a lot of > subtypes, and I know that there will be many more subtypes introduced as > we move along. If a table is meant to hold just a couple of subtypes and > not much variability in that structure is anticipated, i might look into > single table (or combining joined and single) just to reduce complexity. > > > >> Michael Bayer wrote: >> >>> mmmm yeah. OK we only have limited support for that concept right now >>> using of_type(), which currently only supports one type, not a list. >>> so it would be >>> query(Company).join(Company.employees.of_type(Engineer)). In theory >>> of_type() could support a list, but that isnt built right now. So to >>> really get the full SQL you're looking for you'd have to go "old >>> school" and use the table objects here, like: >>> >>> session >>> .query >>> (Company >>> ).select_from >>> (company_table.join(employees_table).outerjoin(engineers_table, >>> <onclause>).outerjoin(managers_table, <onclause>)).<everything else>. >>> >>> you'll actually get a better query from the above since it won't wrap >>> the engineer/manager stuff in a subquery. >>> >>> >>> >>> On Jun 10, 2009, at 8:38 PM, David Gardner wrote: >>> >>> >>> >>>> I was wondering if there was a way to use with_polymorphic() on a >>>> joined >>>> table. >>>> >>>> For instance using the Company->Employees relationship in the >>>> example on >>>> http://www.sqlalchemy.org/docs/05/mappers.html#mapping-class-inheritance-hierarchies >>>> >>>> if I wanted to query for a company, and eagerload the employees and >>>> eagerly join the engineers and managers tables I would think to do >>>> something like: >>>> >>>> session.query(Company).join(Company.employees).\ >>>> options(contains_eager(Company.employees)).\ >>>> with_polymorphic([Engineer, Manager]).\ >>>> filter(Company.name=='test').first() >>>> >>>> >>>> >>>> >>>> >> -- >> David Gardner >> Pipeline Tools Programmer >> Jim Henson Creature Shop >> dgard...@creatureshop.com >> >> >> >> > > > > > > -- David Gardner Pipeline Tools Programmer Jim Henson Creature Shop dgard...@creatureshop.com --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
