Unsure how to specify foreign keys when receiving AmbiguousForeignKeysError

[trippersham]

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]

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

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.

[shaunpatterson]

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

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

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]

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

[doraven]

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]

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