Unsure how to specify foreign keys when receiving AmbiguousForeignKeysError #1542
-
First Check
Commit to Help
Example Codefrom typing import Optional
from uuid import uuid4
from sqlmodel import Field, Session, SQLModel, create_engine, Relationship
class Account(SQLModel, table=True):
id: Optional[str] = Field(default=uuid4, primary_key=True)
institution_id: str
institution_name: str
class Transaction(SQLModel, table=True):
id: Optional[str] = Field(default=uuid4, primary_key=True)
from_account_id: Optional[str] = Field(default=None, foreign_key="account.id")
from_account: Account = Relationship()
to_account_id: Optional[str] = Field(default=None, foreign_key="account.id")
to_account: Account = Relationship()
amount: float
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
SQLModel.metadata.create_all(engine)
account = Account(institution_id='1', institution_name='Account 1')
with Session(engine) as s:
s.add(account)DescriptionWhen creating a table with multiple relationships to another table I am receiving the AmbiguousForeignKeysError SQLAlchemy error. There doesn't appear to be a SQLModel argument for the foreign key on
Not sure how else to pass the right foreign key (possibly using SQLAlchemy's Query API?). Hoping there's a cleaner SQLModel/pydantic way to do this! Operating SystemmacOS Operating System DetailsNo response SQLModel Version0.0.3 Python Version3.9.5 Additional ContextFull stack trace: |
Beta Was this translation helpful? Give feedback.
Replies: 21 comments 1 reply
-
|
Giving my two cents here. The error is being raised by SqlAlchemy so it seems that SqlModel is missing some features to deal with this case, but, it seems to possible to use SqlAlchemy directly as a workaround. Like I said there's still some bugs to that code, when I have some free time i'll try to solve that too. |
Beta Was this translation helpful? Give feedback.
-
|
Appreciate the help @jgbmattos and the SqlLite uuid tip! Looks like the same error I got when using because |
Beta Was this translation helpful? Give feedback.
-
|
EDIT: More simply, skip to my next comment. I ran into this and was desperate for some workaround, so I wrote this gist. There may be a more clever way to do this, but it seems to work for me. I haven't tested with the example above, but in theory you should be able to simply run set_foreign_keys(
Transaction,
{"to_account": "to_account_id", "from_account": "from_account_id"},
)after the class declaration. I hope this helps! EDIT: I modified my code so that it also works if you pass in a |
Beta Was this translation helpful? Give feedback.
-
|
@trippersham, even more simply, it looks like a very slight modification of your attempt actually works! from_account: Account = Relationship(sa_relationship_kwargs=dict(foreign_keys="[Transaction.from_account_id]")) |
Beta Was this translation helpful? Give feedback.
-
|
👇 Runnable code example in the details: from typing import Optional
from uuid import uuid4
from sqlmodel import Field, Session, SQLModel, create_engine, Relationship, select, col
class Account(SQLModel, table=True):
id: Optional[str] = Field(default_factory=lambda: str(uuid4()), primary_key=True)
institution_id: str
institution_name: str
incoming_transactions: list["Transaction"] = Relationship(
back_populates="to_account",
sa_relationship_kwargs={"foreign_keys": "Transaction.to_account_id"},
)
outgoing_transactions: list["Transaction"] = Relationship(
back_populates="from_account",
sa_relationship_kwargs={"foreign_keys": "Transaction.from_account_id"},
)
class Transaction(SQLModel, table=True):
id: Optional[str] = Field(default_factory=lambda: str(uuid4()), primary_key=True)
from_account_id: Optional[str] = Field(default=None, foreign_key="account.id")
from_account: Account = Relationship(
back_populates="outgoing_transactions",
sa_relationship_kwargs={"foreign_keys": "Transaction.from_account_id"},
)
to_account_id: Optional[str] = Field(default=None, foreign_key="account.id")
to_account: Account = Relationship(
back_populates="incoming_transactions",
sa_relationship_kwargs={"foreign_keys": "Transaction.to_account_id"},
)
amount: float
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
SQLModel.metadata.drop_all(engine)
SQLModel.metadata.create_all(engine)
# Add data
with Session(engine) as s:
account1 = Account(institution_id='1', institution_name='Account 1')
account2 = Account(institution_id='2', institution_name='Account 2')
account3 = Account(institution_id='3', institution_name='Account 3')
transaction1 = Transaction(from_account=account1, to_account=account2, amount=10.0)
transaction2 = Transaction(from_account=account2, to_account=account3, amount=5.0)
s.add_all([transaction1, transaction2])
s.commit()
# Check Account 1
with Session(engine) as s:
acc1 = s.exec(select(Account).where(col(Account.institution_id) == "1")).one()
# Incoming transactions (none)
assert len(acc1.incoming_transactions) == 0
# Outgoing transactions (transaction 1 - to "Account 2")
assert len(acc1.outgoing_transactions) == 1
assert acc1.outgoing_transactions[0].to_account.institution_name == "Account 2"
# Check Account 2
with Session(engine) as s:
acc2 = s.exec(select(Account).where(col(Account.institution_id) == "2")).one()
# Incoming transactions (transaction 1 - from "Account 1")
assert len(acc2.incoming_transactions) == 1
assert acc2.incoming_transactions[0].from_account.institution_name == "Account 1"
# Outgoing transactions (transaction 2 - to "Account 3")
assert len(acc2.outgoing_transactions) == 1
assert acc2.outgoing_transactions[0].to_account.institution_name == "Account 3" |
Beta Was this translation helpful? Give feedback.
-
|
Yah I was trying to do something like this but no glory. If anyone has an idea of how to create a resuable mixin such as below and get it woking with The error it returns when attempting to do anything with |
Beta Was this translation helpful? Give feedback.
-
|
@trippersham see #89, where there is a similar problem. As described in the other issue you should be able to solve your issue setting the This should run as-is: from typing import Optional
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine
class Account(SQLModel, table=True):
id: int = Field(primary_key=True)
institution_id: str
institution_name: str
class Transaction(SQLModel, table=True):
id: Optional[int] = Field(primary_key=True)
from_account_id: Optional[str] = Field(default=None, foreign_key="account.id")
from_account: Account = Relationship(
sa_relationship_kwargs={"primaryjoin": "Transaction.from_account_id==Account.id", "lazy": "joined"}
)
to_account_id: Optional[str] = Field(default=None, foreign_key="account.id")
to_account: Account = Relationship(
sa_relationship_kwargs={"primaryjoin": "Transaction.to_account_id==Account.id", "lazy": "joined"}
)
amount: float
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url)
SQLModel.metadata.create_all(engine)
account1 = Account(id=1, institution_id="1", institution_name="Mine")
account2 = Account(id=2, institution_id="2", institution_name="Yours")
transaction = Transaction(id=1, from_account_id=2, to_account_id=1, amount=42)
with Session(engine) as s:
s.add(account1)
s.add(account2)
s.add(transaction)
s.commit()
s.refresh(account1)
s.refresh(account2)
s.refresh(transaction)
print("account1:", account1)
print("account2:", account2)
print("transaction:", transaction) |
Beta Was this translation helpful? Give feedback.
This comment was marked as off-topic.
This comment was marked as off-topic.
-
Thanks your code saved me alot of time @ubersan |
Beta Was this translation helpful? Give feedback.
-
|
Can this be added to the official docs ? |
Beta Was this translation helpful? Give feedback.
-
|
The approach that worked for me is a combination of @ubersan and @maresb 's codes, since I needed to have both forward and backward refs available. Here's a minimal model example: class User(SQLModel, table=True):
# other fields here
documents_created: list["Document"] = Relationship(
back_populates="created_by",
sa_relationship_kwargs={
"primaryjoin": "Document.created_id==User.id",
"lazy": "joined",
},
)
documents_modified: list["Document"] = Relationship(
back_populates="modified_by",
sa_relationship_kwargs={
"primaryjoin": "Document.created_id==User.id",
"lazy": "joined",
},
)
class Document(SQLModel, table=True):
"""Long form document model"""
# other fields here ...
created_by: "User" = Relationship(
back_populates="documents_created",
sa_relationship_kwargs=dict(foreign_keys="[Document.created_id]"),
)
modified_by: "User" = Relationship(
back_populates="documents_modified",
sa_relationship_kwargs=dict(foreign_keys="[Document.modified_id]"),
)NOTE: certain queries now needed to be modified, ie, appended with query = select(User)
user = (await db.execute(query)).unique().scalar_one_or_none() # previously, no `.unique()` required |
Beta Was this translation helpful? Give feedback.
-
class user(SQLModel, table=True):
__tablename__ = "users"
id: Optional[int] = Field(default=None, primary_key=True)
idnumber: Optional[int] = Field(default=None)
name: str = Field(default=None)
email: str = Field(default=None)
created_at: Optional[datetime] = Field(default=None)
updated_at: Optional[datetime] = Field(default=None)
course_participant_relation: List["course_participant"] = Relationship(back_populates="user_relation")
class course_participant(SQLModel, table=True):
__tablename__ = "course_participants"
id: Optional[int] = Field(default=None, primary_key=True)
user_id: int = Field( foreign_key=user.id)
course_id: int = Field(foreign_key=course.id)
group: Optional[str] = Field(default=None)
role: str = Field(default=None)
created_at: Optional[datetime] = Field(default=None)
updated_at: Optional[datetime] = Field(default=None)
user_relation: Optional["user"] = Relationship(back_populates="course_participant_relation")
course_relation: Optional["course"] = Relationship(back_populates="course_with_participant_relation")
participant_evaluator_relation: List["peer_evaluation_record"] = \
Relationship(back_populates="evaluator_participant_relation",
)
participant_evaluator_relation_peer: List["peer_evaluation_record"] = \
Relationship(back_populates="peer_participant_relation")
class peer_evaluation_record(SQLModel, table=True):
__tablename__ = "peer_evaluation_records"
id: Optional[int] = Field(default=None, primary_key=True)
peer_evaluation_id: int = Field(foreign_key=peer_evaluation.id)
evaluator_id: int = Field(foreign_key=course_participant.id)
peer_id: int = Field(foreign_key=course_participant.id)
evaluation: List[int] = Field(default=[0, 0, 0, 0, 0], sa_column=Column(ARRAY(Integer())))
grade: int = Field(default=None)
modified_by: Optional[str] = Field(default=None)
created_at: Optional[datetime] = Field(default=None)
updated_at: Optional[datetime] = Field(default=None)
peer_evaluation_relation: Optional["peer_evaluation"] = Relationship(
back_populates="peer_evaluation_record_relation")
evaluator_participant_relation: Optional["course_participant"] = Relationship(
back_populates="participant_evaluator_relation")
peer_participant_relation: Optional["course_participant"] = Relationship(
back_populates="participant_evaluator_relation_peer")this works fine create table with relation ship foreign keys and all but when i try to insert data it shows error |
Beta Was this translation helpful? Give feedback.
-
Thanks man! You really save my life. I've spent about 2 to 3 hours trying to decide this issue. |
Beta Was this translation helpful? Give feedback.
-
Pay real close attention to the arguments there. I had
|
Beta Was this translation helpful? Give feedback.
-
|
@ohmeow , 2.5 years later, I think I've got a solution to your problem, but it requires a fix in SQLModel (#886). See the test: class CreatedUpdatedMixin(SQLModel):
created_by_id: Optional[int] = Field(default=None, foreign_key="user.id")
created_by: Optional[User] = Relationship(
sa_relationship=declared_attr(
lambda cls: relationship(User, foreign_keys=cls.created_by_id)
)
)
updated_by_id: Optional[int] = Field(default=None, foreign_key="user.id")
updated_by: Optional[User] = Relationship(
sa_relationship=declared_attr(
lambda cls: relationship(User, foreign_keys=cls.updated_by_id)
)
)
class Asset(CreatedUpdatedMixin, table=True):
id: Optional[int] = Field(default=None, primary_key=True) |
Beta Was this translation helpful? Give feedback.
-
Only the approach from this comment with the class OneWithFK:
example_id: int = Field(foreign_key="target_classes.id")
example: TargetClass = Relationship(
sa_relationship=RelationshipProperty(
"TargetClass",
foreign_keys="[OneWithFK.example_id]",
)
)This should really be in the docs, since it is not uncommon to have two foreign keys linking to another particularly table. If I open a PR, can we merge this example to the docs? |
Beta Was this translation helpful? Give feedback.
This comment was marked as disruptive content.
This comment was marked as disruptive content.
-
|
Minimal test as below, works for me. from sqlmodel import Relationship, SQLModel, Field, create_engine, Session, select
from sqlalchemy.orm import RelationshipProperty
from typing import Optional
class User(SQLModel, table=True):
id: int = Field(default=None, primary_key=True)
name: str
create_codes: list["InvCode"] = Relationship(
sa_relationship=RelationshipProperty(
"InvCode",
back_populates="create_user",
foreign_keys="[InvCode.create_user_id]")
)
used_code: Optional["InvCode"] = Relationship(
sa_relationship=RelationshipProperty(
"InvCode",
back_populates="used_user",
foreign_keys="[InvCode.used_user_id]")
)
class InvCode(SQLModel, table=True):
id: int = Field(default=None, primary_key=True)
content: str = Field(index=True)
create_user_id: int = Field(index=True, foreign_key="user.id")
used_user_id: int | None = Field(foreign_key="user.id")
create_user: User = Relationship(
sa_relationship=RelationshipProperty(
"User",
back_populates="create_codes",
foreign_keys='[InvCode.create_user_id]'))
used_user: Optional['User'] = Relationship(
sa_relationship=RelationshipProperty(
"User",
back_populates="used_code",
foreign_keys='[InvCode.used_user_id]'))
engine = create_engine("sqlite:///./test.db")
SQLModel.metadata.create_all(engine)
def create_db():
with Session(engine) as session:
user1 = User(name="user1")
user2 = User(name="user2")
session.add(user1)
session.add(user2)
session.commit()
invcode1 = InvCode(content="invcode-1-samplestr",
create_user=user1, used_user=user2)
invcode2 = InvCode(content="invcode-2-samplestr", create_user=user1)
session.add(invcode1)
session.add(invcode2)
session.commit()
def read_user():
with Session(engine) as session:
user1 = session.exec(
select(User).where(User.name == "user1")
).one()
print(user1.create_codes)
if __name__ == "__main__":
create_db()
# read_user()
|
Beta Was this translation helpful? Give feedback.
-
|
Thanks for all the examples. It helped me realize how to fulfill my use case - two entities making a trade and capturing that in a join table, as there is a possibility of more than 2 partners in a single trade. (I'll be adding in assets later and that would also likely confuse the situation. Here's the code I came up with: class TradeTeamLink(SQLModel, table=True):
trade_id: int = Field(default=None, primary_key=True, foreign_key="trade.id")
sending_team_id: int = Field(default=None, primary_key=True, foreign_key="team.id")
receiving_team_id: int = Field(default=None, primary_key=True, foreign_key="team.id")
trade: "Trade" = Relationship(back_populates="team_links")
sending_team: "Team" = Relationship(
sa_relationship=RelationshipProperty(
"Team",
back_populates="sent_trades",
foreign_keys="[TradeTeamLink.sending_team_id]")
)
receiving_team: "Team" = Relationship(
sa_relationship=RelationshipProperty(
"Team",
back_populates="received_trades",
foreign_keys="[TradeTeamLink.receiving_team_id]")
)
class Team(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str
city: str
full_name: str
acronym: str = Field(unique=True)
sent_trades: list["TradeTeamLink"] = Relationship(
sa_relationship=RelationshipProperty(
"TradeTeamLink",
back_populates="sending_team",
foreign_keys="[TradeTeamLink.sending_team_id]")
)
received_trades: list["TradeTeamLink"] = Relationship(
sa_relationship=RelationshipProperty(
"TradeTeamLink",
back_populates="receiving_team",
foreign_keys="[TradeTeamLink.receiving_team_id]"
)
)
class Trade(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
trade_date: datetime.date = Field(default=datetime.date.today())
status: str = Field(default="Proposed")
team_links: list[TradeTeamLink] = Relationship(back_populates="trade")Eventually this POST command will be a bit more complicated to add more than 2 teams and also add in the assets, but here's how it gets used in the FastAPI main @app.post("/trade", response_class=HTMLResponse)
async def post_trade(request: Request, team1_id: Annotated[str, Form()], team2_id: Annotated[str, Form()]):
# add the trade to the trades list
with Session(engine) as session:
trade = Trade()
# two team trade, each trade link is a sending team and a receiving team
team1 = session.exec(select(Team).where(Team.id == team1_id)).first()
team2 = session.exec(select(Team).where(Team.id == team2_id)).first()
trade_link = TradeTeamLink(trade=trade, sending_team=team1, receiving_team=team2)
trade_link2 = TradeTeamLink(trade=trade, sending_team=team2, receiving_team=team1)
session.add(trade)
session.add(trade_link)
session.add(trade_link2)
session.commit()
trades = session.exec(select(Trade).options(selectinload(Trade.team_links))).all()
# return the trade.html template
return templates.TemplateResponse(request=request, name="trades.html", context={"request": request, "trades": trades}) |
Beta Was this translation helpful? Give feedback.
-
|
Considering this is an extremely common database scenario (i.e., 1 user with a billing address and shipping address both connecting to an address table) and that sqlalchemy can solve this very easily... https://docs.sqlalchemy.org/en/20/orm/join_conditions.html#handling-multiple-join-paths This is a pretty basic feature and definitely has me questioning using SQLModel. I mean, only defining my models once instead of twice is nice and all (though of course each db/api model actually means ~ 3 classes...) but there are so many stupid little workarounds needed... And so many things that seem to rely on annotations not being imported from |
Beta Was this translation helpful? Give feedback.
This comment has been hidden.
This comment has been hidden.
-
|
Just use Hope this will work for you, I got an another problem thanks for the answers. |
Beta Was this translation helpful? Give feedback.
@trippersham, even more simply, it looks like a very slight modification of your attempt actually works!