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.
