Consider these 2 mapped classes from sqlalchemy.engine import create_engine from sqlalchemy.ext.associationproxy import association_proxy from sqlalchemy.ext.declarative.api import declarative_base from sqlalchemy.orm import relationship from sqlalchemy.orm.session import sessionmaker from sqlalchemy.orm.util import aliased from sqlalchemy.schema import Column, ForeignKey from sqlalchemy.sql.expression import and_ from sqlalchemy.types import Integer, String
Base = declarative_base() class A(Base): __tablename__ = 'table_a' id = Column(Integer, primary_key=True) child_id = Column(Integer, ForeignKey('table_a.id')) children = relationship('A', backref = 'parent', remote_side=[id]) class B(Base): __tablename__ = 'table_b' id = Column(Integer, primary_key=True) a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True) a_re = relationship('A', backref='b_re') What I want to do is have a self-referential relationship in B that routes through A's children relationship while keeping B.id the same. More explicitly I want some relationship B.children such that for any instance beta of B for b in beta.children: assert b.id == beta.id assert b.a_re in beta.a_re.children Now, if the condition b.id == beta.id is ignored, then it's just a plain association table many-to-many relationship, something like B.children = relationship('B', secondary=A.__table__, primaryjoin=B.a_id==A.id, secondaryjoin=B.a_id==A.child_id, viewonly=True) But with the b.id == beta.id condition I need to refer to table_b twice in the join table_b JOIN table_a JOIN table_b, and I'm not sure how to do that in relationship. I've tried this BB = aliased(B) B.children = relationship('BB', secondary=A.__table__, primaryjoin=B.a_id==A.id, secondaryjoin='''and_(A.id==BB.a_id, B.id==BB.id)''', viewonly=True) but it seems like BB is not recognized by the ORM in mapping. How do I do this? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.