python - sqlalchemy polymorphic many-to-many

Question

I'm looking to have a list of objects belong to a parent class, in the following manner:

class A(object):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    collection = relationship(.....) # contains an ordered list of [B, C, B, B, C, C, C, B, C, C, ...];

class B(object):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)

class C(object):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)

The SQLAlchemy examples folder has a simple many-to-one where, in my example, classes B and C are "parent" classes of A (rather than the other way around), but I can't for the life of me work out how to reverse this into a one-to-many, and then add a bidirectional relationship so that it becomes many-to-many.

Can anyone help me out with this?

Answer 1

it's a little painful and AbstractConcreteBase apparently needs a bit more polish, but it's like this:

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

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)

class BC(AbstractConcreteBase, Base):
    pass

class B(BC):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)

    a_id = Column(Integer, ForeignKey('a.id'))
    __mapper_args__ = {
        "polymorphic_identity": "b",
        "concrete": True
    }

class C(BC):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'))
    __mapper_args__ = {
        "polymorphic_identity": "c",
        "concrete": True
    }

configure_mappers()
A.collection = relationship(BC, primaryjoin=BC.a_id == A.id)

engine = create_engine("sqlite://", echo=True)

Base.metadata.create_all(engine)

sess = Session(engine)

sess.add_all([
    A(collection=[
        B(),
        C(),
        C()
    ]),
    A(collection=[
        B(),
        B()
    ])
])

sess.commit()

for a in sess.query(A):
    for bc in a.collection:
        print a, bc