Can I make a relationship with table C, which contains a foreign key to table B, which contains a foreign key to table A, from C to A? Something like the following:
class A(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
b_id: int | None = Field(default=None, foreign_key="b.id")
b: 'B' = Relationship(back_populates='as')
c: 'C' = Relationship() # Somehow have this automatically populated through b.c_id?
class B(SQLModel, table=True):
id: id | None = Field(default=None, primary_key=True)
c_id: int | None = Field(default=None, foreign_key='c.id)
as: list[A] = Relationship(back_populates='b')
c: 'C' = Relationship(back_populates='bs')
class C(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
bs: list[B] = Relationship(back_populates='c')
I’m not even sure where to start with this. I tried just doing it and it threw an error for no foreign keys for A.c.
Michael Piccirillo is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
SQLModel library does not automatically populate these relationships. So you need to explicitly define the relationships and then manually access them.
-
Define the models with relationships
-
Access relationships manually:
from sqlmodel import Session, create_engine, selectCreate an SQLite database in memory (for testing purposes)
engine = create_engine(“sqlite://”)
Create the tables
SQLModel.metadata.create_all(engine)
Example of inserting data
with Session(engine) as session:
# Create a C instance
c_instance = C()
session.add(c_instance)
session.commit()# Create a B instance linked to C b_instance = B(c_id=c_instance.id) session.add(b_instance) session.commit() # Create an A instance linked to B a_instance = A(b_id=b_instance.id) session.add(a_instance) session.commit()Query the database and access relationships
with Session(engine) as session:
a_instance = session.exec(select(A)).first()
b_instance = a_instance.b
c_instance = b_instance.c
print(f”A -> B ID: {b_instance.id}, B -> C ID: {c_instance.id}”)