Unsure how to specify foreign keys when receiving AmbiguousForeignKeysError

trippersham commented 3 years ago

First Check

[X] I added a very descriptive title to this issue.
[X] I used the GitHub search to find a similar issue and didn't find it.
[X] I searched the SQLModel documentation, with the integrated search.
[X] I already searched in Google "How to X in SQLModel" and didn't find any information.
[X] I already read and followed all the tutorial in the docs and didn't find an answer.
[X] I already checked if it is not related to SQLModel but to Pydantic.
[X] I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

[X] 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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

@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 commented 3 years ago

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 commented 2 years ago

@trippersham see https://github.com/tiangolo/sqlmodel/issues/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 commented 2 years ago

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 commented 2 years ago

@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 commented 2 years ago

Can this be added to the official docs ?

Pk13055 commented 1 year ago

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 commented 1 year ago

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 commented 9 months ago

@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.

shaunpatterson commented 9 months ago

@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.

Pay real close attention to the arguments there. I had

['Transaction.from_account_id'] not '[Transaction.from_account_id]'

earshinov commented 7 months ago

@ohmeow , 2.5 years later, I think I've got a solution to your problem, but it requires a fix in SQLModel (https://github.com/tiangolo/sqlmodel/pull/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)

benglewis commented 7 months ago

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.

Only the approach from this comment with the sa_relationship= string and the RelationshipProperty argument with the target class name and foreign keys actually worked for me, and I still had to use the string format for the foreign_keys, e.g.

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?

doraven commented 5 months ago

let me say some shitful words. sqlmodel should do this with a more pythonic way. I only saw ugly codes here.

doraven commented 5 months ago

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

dannypage commented 3 months ago

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.py code.

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

jonyscathe commented 1 month ago

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 Surely there is some need for this to be handled within SQLModel somehow?

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 __future__ which is a little scary

ktmsulaim commented 1 month ago

When using SQLModel with multiple foreign key relationships between two tables, you may encounter an AmbiguousForeignKeysError. This occurs when SQLAlchemy can't automatically determine which foreign key to use for a relationship.

Error Message: Copysqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship User.leave_requests - 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.

Solution: The fix involves explicitly specifying the foreign keys and join conditions in the model definitions. Here's how to resolve it:

In the model with multiple foreign keys pointing to the same table (e.g., LeaveRequest), keep the relationship definitions simple:

class LeaveRequest(SQLModel, table=True):
    user_id: Optional[int] = Field(default=None, foreign_key="users.id")
    approved_by: Optional[int] = Field(foreign_key="users.id")
    # ... other fields ...

    user: "User" = Relationship(back_populates="leave_requests")
    approved_user: "User" = Relationship(back_populates="approved_leave_requests")
    # ... other relationships ...

In the referenced model (e.g., User), explicitly specify the foreign keys and join conditions:

class User(SQLModel, table=True):
    # ... fields ...

    leave_requests: List["LeaveRequest"] = Relationship(
        back_populates="user",
        sa_relationship_kwargs={"foreign_keys": "[LeaveRequest.user_id]"}
    )
    approved_leave_requests: List["LeaveRequest"] = Relationship(
        back_populates="approved_user",
        sa_relationship_kwargs={
            "foreign_keys": "[LeaveRequest.approved_by]",
            "primaryjoin": "User.id == LeaveRequest.approved_by"
        }
    )
    # ... other relationships ...

This approach clearly defines which foreign key should be used for each relationship, resolving the ambiguity for SQLAlchemy. Key Points:

Use sa_relationship_kwargs to specify foreign_keys and primaryjoin when needed. Define these on the "many" side of a one-to-many relationship (usually the referenced model). Ensure that the back_populates attributes match on both sides of the relationship.

By following this pattern, you can establish multiple relationships between the same tables without encountering the AmbiguousForeignKeysError.

fastapi / sqlmodel