SQLAlchemy Quesiton: JOIN clause relationships with Many-To-Many association tables and joined table inheritance

sqlalchemy_question_join_mtm_pm.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, relationship, backref, sessionmaker
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.schema import ForeignKey

Base = declarative_base()

################# START Working Example
class A(Base):
    __tablename__ = 'a'

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

    b_list = relationship(
        'B',
        secondary='a_b_association',
        backref='a_list'
    )


class B(Base):
    __tablename__ = 'b'

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

    c_list = relationship(
        'C',
        secondary='b_c_association',
        backref='b_list'
    )


class C(Base):
    __tablename__ = 'c'

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

    # Referece to all As for conveniance
    a_list = relationship(
        "A",
        secondary="join(BCAssociation, B, BCAssociation.b_id == B.id).join(ABAssociation, ABAssociation.b_id == B.id)",
        primaryjoin="C.id == BCAssociation.c_id",
        secondaryjoin="A.id == ABAssociation.a_id",
        viewonly=True, backref=backref('c_list')
    )
################### END Working Example


################# START Failing Example
# class Object(Base):
#     """ Object ORM """
#     __tablename__ = 'object'

#     type = Column(String(30))
#     __mapper_args__ = {
#         'polymorphic_identity': 'object',
#         'polymorphic_on': type
#     }

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

#     def __repr__(self):
#         return "<Object(id='%s', name='%s')>" % (self.id, self.name)


# class A(Object):
#     __tablename__ = 'a'

#     __mapper_args__ = {
#         'polymorphic_identity': 'a',
#     }

#     id = Column(Integer, ForeignKey('object.id'), primary_key=True)

#     b_list = relationship(
#         'B',
#         secondary='a_b_association',
#         backref='a_list'
#     )

# class B(Object):
#     __tablename__ = 'b'

#     __mapper_args__ = {
#         'polymorphic_identity': 'b',
#     }

#     id = Column(Integer, ForeignKey('object.id'), primary_key=True)

#     c_list = relationship(
#         'C',
#         secondary='b_c_association',
#         backref='b_list'
#     )


# class C(Object):
#     __tablename__ = 'c'

#     __mapper_args__ = {
#         'polymorphic_identity': 'c',
#     }

#     id = Column(Integer, ForeignKey('object.id'), primary_key=True)

#     # Referece to all As for conveniance
#     a_list = relationship(
#         "A",
#         secondary="join(BCAssociation, B, BCAssociation.b_id == B.id).join(ABAssociation, ABAssociation.b_id == B.id)",
#         primaryjoin="C.id == BCAssociation.c_id",
#         secondaryjoin="A.id == ABAssociation.a_id",
#         viewonly=True, backref=backref('c_list')
#     )
############### END Failing Example

class ABAssociation(Base):
    __tablename__ = 'a_b_association'

    a_id = Column(Integer, ForeignKey('a.id'), primary_key=True)
    b_id = Column(Integer, ForeignKey('b.id'), primary_key=True)


class BCAssociation(Base):
    __tablename__ = 'b_c_association'

    b_id = Column(Integer, ForeignKey('b.id'), primary_key=True)
    c_id = Column(Integer, ForeignKey('c.id'), primary_key=True)


engine = create_engine('sqlite:///:memory:', echo=True)

Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

a = A(name='a1')
session.add(a)
b = B(name='b1', a_list=[a])
session.add(b)
c = C(name='c1', b_list=[b])
session.add(c)

session.commit()

for c in session.query(C).all():
    print '\n\n\n'
    for a in c.a_list:
        print a.name

https://groups.google.com/forum/#!topic/sqlalchemy/4oHbw7Gg9C4