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.
