Unsure how to specify foreign keys when receiving AmbiguousForeignKeysError

[trippersham]

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 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)

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 using Relationship(sa_relationship_kwargs={'foreign_keys':...}), but neither are a SQLAlchemy Column

• the SQLModel/pydantic field (a FieldInfo object)
• that field's field_name.sa_column (a PydanticUndefined object 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:

2021-08-24 22:28:57,351 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-08-24 22:28:57,352 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("account")
2021-08-24 22:28:57,352 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-08-24 22:28:57,352 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("transaction")
2021-08-24 22:28:57,352 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-08-24 22:28:57,352 INFO sqlalchemy.engine.Engine COMMIT
Traceback (most recent call last):
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2744, in _determine_joins
    self.primaryjoin = join_condition(
  File "<string>", line 2, in join_condition
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/sql/selectable.py", line 1184, in _join_condition
    cls._joincond_trim_constraints(
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/sql/selectable.py", line 1305, in _joincond_trim_constraints
    raise exc.AmbiguousForeignKeysError(
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'transaction' and 'account'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/trippwickersham/Projects/village/gh_issue.py", line 27, in <module>
    account = Account(institution_id='1', institution_name='Account 1')
  File "<string>", line 4, in __init__
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/orm/state.py", line 474, in _initialize_instance
    manager.dispatch.init(self, args, kwargs)
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 343, in __call__
    fn(*args, **kw)
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3565, in _event_on_init
    instrumenting_mapper._check_configure()
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 1873, in _check_configure
    _configure_registries({self.registry}, cascade=True)
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3380, in _configure_registries
    _do_configure_registries(registries, cascade)
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3419, in _do_configure_registries
    mapper._post_configure_properties()
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 1890, in _post_configure_properties
    prop.init()
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/orm/interfaces.py", line 222, in init
    self.do_init()
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2142, in do_init
    self._setup_join_conditions()
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2238, in _setup_join_conditions
    self._join_condition = jc = JoinCondition(
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2633, in __init__
    self._determine_joins()
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2796, in _determine_joins
    util.raise_(
  File "/Users/trippwickersham/opt/miniconda3/envs/village/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Transaction.from_account - there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

[jgbmattos]

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.
Based on this thread I was able to create both tables with the correct relationship, but, to be honest there's still a issue happening when trying to use those tables.
Another thing is, SqlLite doesn't support uuid directly like postgres so you'll need to work with strings.

class Account(SQLModel, table=True):
    id: Optional[str] = Field(primary_key=True)
    institution_id: str
    institution_name: str


class AccountTransaction(SQLModel, table=True):
    id: Optional[str] = Field(primary_key=True)
    from_account_id: str = Field(foreign_key="account.id")
    from_account: Account = Relationship(sa_relationship=RelationshipProperty("Account", foreign_keys=[from_account_id]))
    to_account_id: str = Field(foreign_key="account.id")
    to_account: Account = Relationship(sa_relationship=RelationshipProperty("Account", foreign_keys=[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.create_all(engine)

Like I said there's still some bugs to that code, when I have some free time i'll try to solve that too.

[trippersham]

Appreciate the help @jgbmattos and the SqlLite uuid tip! Looks like the same error I got when using

from_account_id: str = Field(foreign_key="account.id")
from_account: Account = Relationship(sa_relationship_kwargs={'foreign_keys':[from_account_id]})

because from_account_id is a SQLModelFieldInfo, not a SQLAlchemy Column. FieldInfo has an sa_column attribute but it's undefined at this point. Hopefully that sparks a potential solution I haven't thought of yet.

[maresb]

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 Column object. This is useful in case there is a foreign key in a different table (e.g. with a link model).

[maresb]

@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]"))

[ohmeow]

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 SQLModel, I'm all ears ....

class UpsertByModelMixin(SQLModel):
    created_by_id : Optional[int] = Field(default=None, foreign_key="users.id")
    created_by: Optional["User"] = Relationship(sa_relationship_kwargs={ 'foreign_keys': [created_by_id] })
    
    updated_by_id : Optional[int] = Field(default=None, foreign_key="users.id")
    updated_by: Optional["User"] = Relationship(sa_relationship_kwargs={ 'foreign_keys': [updated_by_id] })


class Team(UpsertByModelMixin, SQLModel, table=True,):
    __tablename__ = 'teams'
    
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(max_length=500)

The error it returns when attempting to do anything with Team:

sqlalchemy.exc.ArgumentError: Column expression expected for argument 'foreign_keys'; got FieldInfo(default=PydanticUndefined, extra={'exclude': None, 'include': None}).

[ubersan]

@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 primaryjoin instead of the foreign_keys property. ("lazy": "joined" just added for complete output - see (https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html#configuring-loader-strategies-at-mapping-time))

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)

[productdevbook]

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 SQLModel, I'm all ears ....

class UpsertByModelMixin(SQLModel):
    created_by_id : Optional[int] = Field(default=None, foreign_key="users.id")
    created_by: Optional["User"] = Relationship(sa_relationship_kwargs={ 'foreign_keys': [created_by_id] })
    
    updated_by_id : Optional[int] = Field(default=None, foreign_key="users.id")
    updated_by: Optional["User"] = Relationship(sa_relationship_kwargs={ 'foreign_keys': [updated_by_id] })


class Team(UpsertByModelMixin, SQLModel, table=True,):
    __tablename__ = 'teams'
    
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(max_length=500)

The error it returns when attempting to do anything with Team:

sqlalchemy.exc.ArgumentError: Column expression expected for argument 'foreign_keys'; got FieldInfo(default=PydanticUndefined, extra={'exclude': None, 'include': None}).

some problem fixed ?

[Baghdady92]

@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 primaryjoin instead of the foreign_keys property. ("lazy": "joined" just added for complete output - see (https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html#configuring-loader-strategies-at-mapping-time))

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)

Thanks your code saved me alot of time @ubersan

[eznix86]

Can this be added to the official docs ?

[Pk13055]

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 .unique() at the end cause of the join condition

query = select(User)
user = (await db.execute(query)).unique().scalar_one_or_none()  # previously, no `.unique()` required

[rajatayyab3]

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

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
pecify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

[plamer777]

@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]"))

Thanks man! You really save my life. I've spent about 2 to 3 hours trying to decide this issue.