I'm trying to create a relationship that spans across 3 tables but I can't quite figure out the syntax.
I have 3 tables TableA
, TableB
and TableC
and the relationship I'm trying to model is:
TableA.my_relationship = relationship(
'TableC',
primaryjoin='and_(TableA.fk == TableB.pk, TableB.fk == TableC.pk)',
viewonly=True
)
so that on an instance of TableA
I can do instance_of_a.my_relationship
to get the TableC
record that's indirectly associated with instance_of_a
Python Flask and SQLAlchemy ORM Many to Many relationship between two tables is achieved by adding an association table such that it has two foreign keys - one from each table's primary key.
Usually, the Table uses the MetaData object associated with the declarative base class, so that the ForeignKey directives can locate the remote tables with which to link. The relationship. back_populates parameter for each relationship() establishes a bidirectional relationship.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
b_id = Column(Integer, ForeignKey('b.id'))
# method one - put everything into primaryjoin.
# will work for simple lazy loads but for eager loads the ORM
# will fail to build up the FROM to correctly include B
cs = relationship("C",
# C.id is "foreign" because there can be many C.ids for one A.id
# B.id is "remote", it sort of means "this is where the stuff
# starts that's not directly part of the A side"
primaryjoin="and_(A.b_id == remote(B.id), foreign(C.id) == B.c_id)",
viewonly=True)
# method two - split out the middle table into "secondary".
# note 'b' is the table name in metadata.
# this method will work better, as the ORM can also handle
# eager loading with this one.
c_via_secondary = relationship("C", secondary="b",
primaryjoin="A.b_id == B.id", secondaryjoin="C.id == B.c_id",
viewonly=True)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
c_id = Column(Integer, ForeignKey('c.id'))
class C(Base):
__tablename__ = 'c'
id = Column(Integer, primary_key=True)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
sess = Session(e)
sess.add(C(id=1))
sess.flush()
sess.add(B(id=1, c_id=1))
sess.flush()
sess.add(A(b_id=1))
sess.flush()
a1 = sess.query(A).first()
print(a1.cs)
print(a1.c_via_secondary)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With