Currently I'm having a problem when trying to have two different foreign keys to the same Model

[Miguelme]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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 List, Optional
from sqlmodel import Field, SQLModel, Relationship

class GameSessionQuestionLink(SQLModel, table=True):
    game_session_id: int = Field(foreign_key="gamesession.id", primary_key=True)
    question_id: int = Field(foreign_key="question.id", primary_key=True)

class GameSession(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    players: List["Player"] = Relationship(back_populates="game_session")
    questions: List["Question"] = Relationship(link_model=GameSessionQuestionLink)

class Question(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    content: str

class Vote(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    voting_player_id: int = Field(default=None, foreign_key="player.id")
    voted_player_id: int = Field(default=None, foreign_key="player.id")
    question_id: int = Field(default=None, foreign_key="question.id")
    # Specify the relationship for the voting player
    player: "Player" = Relationship(back_populates="votes")
    voted_player: "Player" = Relationship(sa_relationship_kwargs={"foreign_keys": [voted_player_id]})

class Player(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    game_session_id: Optional[int] = Field(default=None, foreign_key="gamesession.id")
    game_session: Optional["GameSession"] = Relationship(back_populates="players")
    votes: List[Vote] = Relationship(back_populates="player")


## That code returns the following error: 

  File "/Users/miguel.fagundez/Desktop/fa/game-fastapi/.venv/lib/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 1884, in _setup_join_conditions
    self._join_condition = jc = JoinCondition(
                                ^^^^^^^^^^^^^^
  File "/.venv/lib/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 2310, in __init__
    self._determine_joins()
  File "/.venv/lib/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 2467, in _determine_joins
    raise sa_exc.AmbiguousForeignKeysError(
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Vote.player - 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.

Description

When trying to have two references to a model as I have in the case of the Vote class to the player class where I want to know who voted but also to who that user voted I'm running into some relationship problems that are displaying that we don't offer the same foreign_keys field offered by SQLAlchemy, which would be the workaround for this?

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.16

Python Version

3.12

Additional Context

No response

[abdulrahman-mh]

Try this:

voted_player: "Player" = Relationship(sa_relationship_kwargs={"foreign_keys": [voted_player_id]})

to

voted_player: "Player" = Relationship(sa_relationship_kwargs={"foreign_keys": "voted_player_id"})

[JoelYoung01]

I had a similar issue and ended up here, finding no answer. I fixed this by using the sa_relationship_kwargs slightly differently, here is a distilled example of what I had:

class ChatSession(SQLModel, table=True):
    id: int | None = Field(default=None, index=True, primary_key=True)
    user_id: int = Field(foreign_key="user.id")
    other_user_id: int = Field(foreign_key="user.id")

    user: "User" = Relationship()
    other_user: "User" = Relationship()

This gave a similar error (though I realize not the same) as op.

sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'Mapper[ChatSessionExample(chatsessionexample)]'. Original exception was: Could not determine join condition between parent/child tables on relationship ChatSessionExample.user - 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.

The following is the result that ended up fixing this error.

class ChatSessionExample(BaseIndexedDbModel, table=True):
    user_id: int = Field(foreign_key="user.id")
    other_user_id: int = Field(foreign_key="user.id")

    user: "User" = Relationship(
        sa_relationship_kwargs={"foreign_keys": "ChatSessionExample.user_id"}
    )
    other_user: "User" = Relationship(
        sa_relationship_kwargs={"foreign_keys": "ChatSessionExample.other_user_id"}
    )

I'm pretty new to SQLModel, so if anyone knows this is not an ideal workaround or a better one exists, please say so! I'll try and update this comment with any necessary changes.