-
-
Notifications
You must be signed in to change notification settings - Fork 763
Closed
Labels
questionFurther information is requestedFurther information is requested
Description
First Check
- I added a very descriptive title to this issue.
- I used the GitHub search to find a similar issue and didn't find it.
- I searched the SQLModel documentation, with the integrated search.
- I already searched in Google "How to X in SQLModel" and didn't find any information.
- I already read and followed all the tutorial in the docs and didn't find an answer.
- I already checked if it is not related to SQLModel but to Pydantic.
- I already checked if it is not related to SQLModel but to SQLAlchemy.
Commit to Help
- I commit to help with one of those options 👆
Example Code
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)
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
Description
I am trying to create a model with multiple many-to-many relationships referencing self. See example code.
My code raises this error:
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship User.parents - there are multiple foreign key paths linking the tables via secondary table 'userlink'. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference from the secondary table to each of the parent and child tables
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:
- Am I correct in the assumption that I need to use many-to-many relationships with a link model?
- ...or could the same be achieved by using a one-to-many with
back_populates
? - The sqlalchemy error is quite cryptic (at least to me). I seem to already have specified the foreign_key on the
UserLink
model columns but apparently I need to specify it differently or somewhere else as well. What else is needed to make this work?
Operating System
Linux
Operating System Details
No response
SQLModel Version
0.0.4
Python Version
3.9.5
Additional Context
No response
maresb, tomliptrot, cashewpillar, gmagno and eliasmistler
Metadata
Metadata
Assignees
Labels
questionFurther information is requestedFurther information is requested