Multiple many-to-many self referencing relationships #1518
-
First Check
Commit to Help
Example Codefrom typing import List, Optional
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine
class UserLink(SQLModel, table=True):
parent_id: Optional[int] = Field(
default=None, foreign_key="user.id", primary_key=True
)
child_id: Optional[int] = Field(
default=None, foreign_key="user.id", primary_key=True
)
class User(SQLModel, table=True):
id: int = Field(default=None, primary_key=True)
parents: List["User"] = Relationship(back_populates="children", link_model=UserLink)
children: List["User"] = Relationship(back_populates="parents", link_model=UserLink)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
user_1 = User(id=1)
session.add(user_1)
user_2 = User(id=2, parents=[user_1])
session.add(user_2)
session.commit()
session.refresh(user_1)
session.refresh(user_2)
assert len(user_1.children) == 1
assert len(user_1.parents) == 0
assert len(user_2.children) == 0
assert len(user_2.parents) == 1 DescriptionI am trying to create a model with multiple many-to-many relationships referencing self. See example code. My code raises this error:
My code is based on the many-to-many example in the docs. As the error is coming from sqlalchemy it might also be an sqlalchemy issue but as it is raised by an example that is coming from the docs I think I might be using SQLmodel incorrectly. I did read the sqlalchemy docs on many-to-many relationships but they did not help me. This issue on stackoverflow seems related but I cannot figure out how to apply it to my situation. Questions:
Operating SystemLinux Operating System DetailsNo response SQLModel Version0.0.4 Python Version3.9.5 Additional ContextNo response |
Beta Was this translation helpful? Give feedback.
Replies: 6 comments
-
I'm fairly new to this, but I seem to have managed to get it to work with class User(SQLModel, table=True):
id: int = Field(default=None, primary_key=True)
parents: List["User"] = Relationship(
back_populates="children",
link_model=UserLink,
sa_relationship_kwargs=dict(
primaryjoin="User.id==UserLink.child_id",
secondaryjoin="User.id==UserLink.parent_id",
),
)
children: List["User"] = Relationship(
back_populates="parents",
link_model=UserLink,
sa_relationship_kwargs=dict(
primaryjoin="User.id==UserLink.parent_id",
secondaryjoin="User.id==UserLink.child_id",
),
) I'm not sure whether or not this is the "correct" way. I'm curious about what more expert people have to say. My inspiration was Self-Referential Many-to-Many Relationship from the SQLAlchemy docs. |
Beta Was this translation helpful? Give feedback.
-
@maresb This works! You are my hero! 🦸 Awesome. I think I tried something similar by passing And thanks for the link to the documentation, I did not find this page before. It really helps to understand what information/configuration SQLmodel/sqlalchemy need to make these self-referential relationships. Thanks a lot! |
Beta Was this translation helpful? Give feedback.
-
I'm glad I could help! None of this stuff is documented on the SQLModel side, and I wouldn't be surprised if tiangolo wants to solve this in some more elegant way. Maybe it's better to leave this issue open until a maintainer has a look? |
Beta Was this translation helpful? Give feedback.
-
That's a good point. I'll reopen the issue. |
Beta Was this translation helpful? Give feedback.
-
I tried the from typing import List, Optional
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine
class UserLink(SQLModel, table=True):
parent_id: Optional[int] = Field(
default=None, foreign_key="user.id", primary_key=True
)
child_id: Optional[int] = Field(
default=None, foreign_key="user.id", primary_key=True
)
class User(SQLModel, table=True):
id: int = Field(default=None, primary_key=True)
parents: List["User"] = Relationship(
back_populates="children",
link_model=UserLink,
sa_relationship_kwargs=dict(
primaryjoin="User.id==UserLink.child_id",
secondaryjoin="User.id==UserLink.parent_id",
),
)
children: List["User"] = Relationship(
back_populates="parents",
link_model=UserLink,
sa_relationship_kwargs=dict(
primaryjoin="User.id==UserLink.parent_id",
secondaryjoin="User.id==UserLink.child_id",
),
)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
user_1 = User(id=1)
session.add(user_1)
user_2 = User(id=2, parents=[user_1])
session.add(user_2)
session.commit()
session.refresh(user_1)
session.refresh(user_2)
assert len(user_1.children) == 1
assert len(user_1.parents) == 0
assert len(user_2.children) == 0
assert len(user_2.parents) == 1 But I get the error AttributeError: 'User' object has no attribute 'children' Where did I make a mistake? |
Beta Was this translation helpful? Give feedback.
-
@Iionsroar I think this is not related to this issue, but this one #322. |
Beta Was this translation helpful? Give feedback.
I'm fairly new to this, but I seem to have managed to get it to work with