Thank you. This was very helpful. One non-trivial thing that stumped me for a while is that if B is derived from a B_base using joined-table inheritance, and the order variable is in the base table B_base, then it seems one must include B_base explicitly -- as highlighted below.
from sqlalchemy import Column, Integer, ForeignKey, create_engine, select, join from sqlalchemy.orm import create_session, relationship, backref, mapper from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.associationproxy import association_proxy sqlite = 'sqlite:///test_a_to_b.db' e = create_engine(sqlite, echo=True) Base = declarative_base(bind=e) class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) bs = association_proxy("a_to_bs", "b", creator=lambda x: A_to_B(b=x)) class B_base(Base): __tablename__ = 'b_base' id = Column(Integer, primary_key=True) order = Column(Integer) class B(B_base): __tablename__ = 'b' id = Column(Integer, ForeignKey(str(B_base.__table__) + ".id"), primary_key=True) class A_to_B(Base): __tablename__ = 'a_to_b' id = Column(Integer, primary_key=True) a_id = Column(Integer, ForeignKey(str(A.__table__) + ".id"), nullable=False, index=True) a = relationship(A, foreign_keys=[a_id], backref=backref("a_to_bs", primaryjoin="and_(A_to_B.a_id == A.id, A_to_B.b_id == B.id*, B.id == B_base.id*)", order_by="B.order")) b_id = Column(Integer, ForeignKey(str(B.__table__) + ".id"), nullable=False) b = relationship(B, foreign_keys=[b_id], lazy="subquery") def __init__(self, a=None, b=None): self.a = a self.b = b if __name__ == '__main__': # recreate database Base.metadata.drop_all() Base.metadata.create_all() # populate session = create_session(bind=e, autocommit=False) a = A(bs=[B(order=10), B(order=2)]) session.add(a) session.commit() session.close_all() # read session = create_session(bind=e, autocommit=False) a = session.query(A).one() for b in a.bs: print b.order for a_to_b in a.a_to_bs: print a_to_b.b.order session.close_all() -- 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. For more options, visit https://groups.google.com/groups/opt_out.