getting it to work with "secondary" or only "primaryjoin" as it sometimes works out is fairly complex and might not be possible. If "secondary", you might need to make "secondary" an aliased SELECT statement, or in 0.9 maybe it can be a a JOIN, that represents all the intermediary rows. Might work, might not, would have to spend a few hours with it.
Is there a reason you can't just route to the related B.a.children.bs using proxies? Or a @property based loader? On Jun 11, 2013, at 4:45 PM, Greg Yang <[email protected]> wrote: > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
