I'd hate to make a bad story worse here, but in fact this is also the same
eager loading pattern used in some other emails I've been answering here by
Martin84...it's very unusual how these never-seen-before use cases suddenly pop
up in pairs..but in any case, the eager loading here is actually broken in all
versions, and it's not even possible to have two same-named attributes eager
loading simultaneously on two subclasses of a with_polymorphic(). I've spent
all day examining a fix, and it is turning out to be not at all trivial. So
for now the new release of 0.8 will flat out raise an exception if this
condition is detected. The eager loads otherwise return the incorrect results
in many/most cases.
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.
>
> from sqlalchemy import *
> from sqlalchemy.ext.declarative import declared_attr, declarative_base
> from sqlalchemy.orm import Session, relationship
>
> Base = declarative_base()
>
> class Test(Base):
> __tablename__ = 'test'
>
> id = Column(Integer, primary_key=True)
> type = Column(String(12))
> name = Column(String(50))
>
> __mapper_args__ = {
> 'polymorphic_identity': 'base',
> 'polymorphic_on': type,
> 'with_polymorphic': '*',
> }
>
> class Extra(Base):
> __tablename__ = 'extra'
>
> id = Column(Integer, ForeignKey(Test.id), primary_key=True)
> extra_data = Column(Text, default='goats')
>
> class ExtraMixin(object):
>
> @declared_attr
> def extra(cls):
> return relationship(Extra, uselist=False, lazy='joined',
> primaryjoin=Extra.id==Test.id,
> foreign_keys=Extra.id,
> )
>
> class TestOne(Test):
> __tablename__ = 'test1'
> __mapper_args__ = { 'polymorphic_identity': 'one' }
>
> id = Column(Integer, ForeignKey(Test.id), primary_key=True)
> value1 = Column(String(16))
>
> class TestTwo(Test, ExtraMixin):
> __tablename__ = 'test2'
> __mapper_args__ = { 'polymorphic_identity': 'two' }
>
> id = Column(Integer, ForeignKey(Test.id), primary_key=True)
> value2 = Column(String(16))
>
> class TestThree(Test, ExtraMixin):
> __tablename__ = 'test3'
> __mapper_args__ = { 'polymorphic_identity': 'three' }
>
> id = Column(Integer, ForeignKey(Test.id), primary_key=True)
> value3 = Column(String(16))
>
>
> if __name__ == '__main__':
>
> import logging
> logging.basicConfig()
> logging.getLogger('sqlalchemy.orm').setLevel(logging.INFO)
>
> e = create_engine('sqlite:////tmp/foo.db', echo=True)
> Base.metadata = MetaData(e)
> Base.metadata.drop_all()
> Base.metadata.create_all()
> session = Session(e)
>
> session.query(Test).all()
> session.query(TestOne).all()
> session.query(TestTwo).all()
>
> 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.
>
> Is there a way to prevent the relationship from being aliased? 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, so the resulting data would be usable for the active
> defined relationships (would I use contains_eager for something like this?).
> What can I do to make the mixed in table behave like the mapped polymorphic
> table, where the join is very clean and not aliased? There is a definite
> ideal join condition I am looking to achieve here, and I don't know how to
> get to it.
>
> 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?
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/Mg8uL-3q0m8J.
> 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.
--
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.
