Clone wiki

sqlalchemy / UsageRecipes / PolymorphicOnAnotherTable

PolymorphicOnAnotherTable

This is supported directly by applying a column_property() as the target of polymorphic_on:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class AType(Base):
    __tablename__ = 'atype'

    id = Column(Integer, primary_key=True)
    name = Column(String)

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    data = Column(String)
    type_id = Column(ForeignKey('atype.id'))
    type_name = column_property(select([AType.name]).where(AType.id == id))
    type = relationship(AType)

    __mapper_args__ = {
        "polymorphic_on": type_name,
        "polymorphic_identity": "a"
    }

class ASub(A):
    __tablename__ = 'asub'

    id = Column(ForeignKey('a.id'), primary_key=True)
    __mapper_args__ = {
        "polymorphic_identity": "asub"
    }

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

sess = Session(e)

a_type, asub_type = AType(name="a"), AType(name="asub")

sess.add_all([
    A(data='a1', type=a_type),
    ASub(data='asub1', type=asub_type),
    ASub(data='asub2', type=asub_type),
    A(data='a2', type=a_type),
])
sess.commit()

sess = Session(e)
for a in sess.query(A):
    print a.data, a.type

Updated