David Gardner wrote: > > OK I think that would work for me. > > I have another question in regards to joined table inheritance and > performance. > At work we are planning to restructure our database schema, and what we > are considering doing is > creating an adjacency list, of objects using joined table inheritance. > We are thinking there would be at least four subtypes, > where each would be a subclass of the previous (B inherits from A, and C > inherits from B). We are doing it this way because > every object could overload a value from its parent (probably won't 90% > of the time). > We would probably want to eagerly load children two levels deep, and at > the lowest level an object would make up a join of four tables. > > The table will be populated with the rows from two existing tables one > with 11,000 rows the other with 40,000 rows, so presumably in the > near future the table would have well over 100,000 rows. > > How bad do you think the performance would be? Is this something that is > just too crazy to try?
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 > [email protected] > > > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
