On Nov 23, 2012, at 12:57 AM, Gerald Thibault wrote: > I have a base class and 3 subclasses which inherit from it polymorphically. I > also have a mixin, which attempts to add a relation to the 'Extra' class, > which has a foreign key to the base class. > > Here is the runnable test. > > > Issuing the first query (note the with_polymorphic on the base) yields this: > > SELECT test.id AS test_id, test.type AS test_type, test.name AS test_name, > test1.id AS test1_id, test1.value1 AS test1_value1, test2.id AS test2_id, > test2.value2 AS test2_value2, test3.id AS test3_id, test3.value3 AS > test3_value3, extra_1.id AS extra_1_id, extra_1.extra_data AS > extra_1_extra_data, extra_2.id AS extra_2_id, extra_2.extra_data AS > extra_2_extra_data > FROM test LEFT OUTER JOIN test1 ON test.id = test1.id LEFT OUTER JOIN test2 > ON test.id = test2.id LEFT OUTER JOIN test3 ON test.id = test3.id LEFT OUTER > JOIN extra AS extra_1 ON extra_1.id = test.id LEFT OUTER JOIN extra AS > extra_2 ON extra_2.id = test.id > > The extra table is being joined twice, because two subclasses inherit the > relationship to the Extra class.
the first observation to make here is that this is an overuse of joined table inheritance. We can try to optimize individual cases but at the end of the day, establishing the mapping such that all classes are dependent on a base is going to lead to lots of joins, each of which adds performance latency. with_polymorphic='*' as a default behavior particularly. So we're starting with an approach that's guaranteed to lead to non-performant queries. > > Is there a way to prevent the relationship from being aliased? the relationship has to be aliased since this is how joined loading works, unless you construct the joins manually and combine it with contains_eager() . See http://docs.sqlalchemy.org/en/rel_0_8/orm/loading.html#the-zen-of-eager-loading and http://docs.sqlalchemy.org/en/rel_0_8/orm/loading.html#routing-explicit-joins-statements-into-eagerly-loaded-collections. Aliasing is not a problem. It's the multiple joins you don't want. > This is a one-to-one relation, so ideally a polymorphic query would issue > left outer joins once for every involved table, all joined from Test.id, and > it wouldn't alias any of them, there is no chance of SQLAlchemy ever knowing how to do that automatically, so you'd need to stick the relationship on the base object here or construct the joins manually. > > Would I need to forego using eagerjoined relations due to the aliasing of the > joined tables? If so, would I need to iteratively join new tables to > mapper.mapped_table? I've tried that already, and ran into problems with > Extra.id not being recognized as a key that can fold into the other > equivalent keys, and it throws an error while trying to autogen the columns > from the mapped_table. Is there a way to specify beforehand that certain > columns should be folded into existing ones? I'm not parsing this completely (autogen the columns from the mapped_table...), but overall my impression is that there's a battle here between purity (having Extra applied to only those individual Test subclasses that need it, applied exactly the same way to each one, making sure there isn't an unused "extra" attribute on those Test subclasses that don't need it) and practicality (just put .extra on the base, everything works, some of your Test subclasses have an "extra" attribute that is unused). The foreign key relationship here is between Extra and Test, so IMHO it belongs on the base. If that is failing, then send that test case along. -- 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.
