Unsure how to specify foreign keys when receiving AmbiguousForeignKeysError #10
Comments
|
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. |
|
Appreciate the help @jgbmattos and the SqlLite uuid tip! Looks like the same error I got when using because |
|
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 |
|
@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]")) |
|
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 |
|
@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) |
some problem fixed ? |
Thanks your code saved me alot of time @ubersan |
|
Can this be added to the official docs ? |
fix: enum fixes (typing and inheritance)
|
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 |
|
class user(SQLModel, table=True): class course_participant(SQLModel, table=True): class peer_evaluation_record(SQLModel, table=True): this works fine create table with relation ship foreign keys and all but when i try to insert data it shows error sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship course_participant.participant_evaluator_relation - there are multiple foreign key paths linking the tables. S |
Thanks man! You really save my life. I've spent about 2 to 3 hours trying to decide this issue. |
Pay real close attention to the arguments there. I had
|
|
@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) |
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? |
|
let me say some shitful words. sqlmodel should do this with a more pythonic way. I only saw ugly codes here. |
|
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()
|
First Check
Commit to Help
Example Code
Description
When 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
Relationship. I tried passing the following to SQLAlchemy usingRelationship(sa_relationship_kwargs={'foreign_keys':...}), but neither are a SQLAlchemyColumnFieldInfoobject)field_name.sa_column(aPydanticUndefinedobject at this point in initialization)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 System
macOS
Operating System Details
No response
SQLModel Version
0.0.3
Python Version
3.9.5
Additional Context
Full stack trace:
The text was updated successfully, but these errors were encountered: