How to Resolve Foreign Key Ambiguity in SQLModel Relationships with Multiple Foreign Keys

[dijub]

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

class User_Subfolder_Permission(SQLModel):
    id_user: int = Field(foreign_key="user_profile.id")
    requestor_user_id: int = Field(foreign_key="user_profile.id")
    subfolder_id: int = Field(foreign_key="subfolder.id")


class Subfolder(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    sub_folder: str
    users: list["User_Profile"] = Relationship(back_populates="subfolders_with_permission", link_model=User_Subfolder_Permission)

class User_Profile(User_ProfileBase, table=True):
    id: int = Field(default=None, primary_key=True)
    subfolders_with_permission: list["Subfolder"] = Relationship(back_populates="users", link_model=User_Subfolder_Permission, foreign_keys=["id_user"])

Description

I am encountering an issue when configuring a relationship between tables using SQLModel. I receive the following error message:

"Could not determine join condition between parent/child tables on relationship Subfolder.users - there are multiple foreign key paths linking the tables via secondary table 'user_subfolder_permission'. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference from the secondary table to each of the parent and child tables."

How can I specify the foreign key correctly when there are two foreign keys to the same table? Below are the models I am using:

class User_Subfolder_Permission(SQLModel):
    id_user: int = Field(foreign_key="user_profile.id")
    requestor_user_id: int = Field(foreign_key="user_profile.id")
    subfolder_id: int = Field(foreign_key="subfolder.id")


class Subfolder(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    sub_folder: str
    users: list["User_Profile"] = Relationship(back_populates="subfolders_with_permission", link_model=User_Subfolder_Permission)

class User_Profile(User_ProfileBase, table=True):
    id: int = Field(default=None, primary_key=True)
    subfolders_with_permission: list["Subfolder"] = Relationship(back_populates="users", link_model=User_Subfolder_Permission, foreign_keys=["id_user"])


### Operating System

Linux

### Operating System Details

_No response_

### SQLModel Version

0.018

### Python Version

3.10.12

### Additional Context

_No response_

[jonyscathe]

Have you had any luck solving this?
I have the exact same issue.
Easily solvable in SQLAlchemy: https://docs.sqlalchemy.org/en/20/orm/join_conditions.html#handling-multiple-join-paths
But there is no way to pass through a foreign_key into Relationship.
It is possible it pass either an sa_relationship or sa_relationship args/kwargs through, but at the time of defining the SQLModel class I don't think we have access to the Column object that we need to pass through... so that makes life a bit difficult....

This is a really basic database type relationship (example on sqlalchemy website of a billing address/shipping address is very common...
So hopefully there is some way to do this.

[AyrtonDare]

I'm running into a similar problem with a link table, so far I've tried 3 approaches based on the docs/issues/discussions. I've attempted it with plain sqlmodel, sqlachemy enriched sqlmodel, and linked tables from sqlmodel but no luck with any of them.

Option 1

from uuid import UUID, uuid4
from sqlmodel import SQLModel, Field, Relationship

class ParentChildLinkTable(SQLModel, table=True):
    __tablename__ = 'parent_child_link'
    parent_id: UUID = Field(foreign_key="nodes.id", primary_key=True)
    child_id: UUID = Field(foreign_key="nodes.id", primary_key=True)
    parent: 'Node' = Relationship(back_populates="children")
    child: 'Node' = Relationship(back_populates="parents")

class Node(SQLModel, table=True):
    __tablename__ = 'nodes'
    id: UUID = Field(primary_key=True)
    name: str
    parents: list['Node'] = Relationship(back_populates="child")
    children: list['Node'] = Relationship( back_populates="parent")

node_1 = Node(id=uuid4(), name='Node-1')

Results in

AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship ParentChildLinkTable.parent - 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.

Option 2

from uuid import UUID, uuid4
from sqlmodel import SQLModel, Field, Relationship

class ParentChildLinkTable(SQLModel, table=True):
    __tablename__ = 'parent_child_link'
    parent_id: UUID = Field(foreign_key="nodes.id", primary_key=True)
    child_id: UUID = Field(foreign_key="nodes.id", primary_key=True)
    parent: 'Node' = Relationship(back_populates="children", sa_relationship_kwargs={"foreign_keys": "ParentChildLinkTable.parent_id"})
    child: 'Node' = Relationship(back_populates="parents", sa_relationship_kwargs={"foreign_keys": "ParentChildLinkTable.child_id"})

class Node(SQLModel, table=True):
    __tablename__ = 'nodes'
    id: UUID = Field(primary_key=True)
    name: str
    parents: list['Node'] = Relationship(back_populates="child", sa_relationship_kwargs={"foreign_keys": "ParentChildLinkTable.parent_id"})
    children: list['Node'] = Relationship( back_populates="parent", sa_relationship_kwargs={"foreign_keys": "ParentChildLinkTable.child_id"})

node_1 = Node(id=uuid4(), name='Node-1')

Results in

ArgumentError: reverse_property 'children' on relationship ParentChildLinkTable.parent references relationship Node.children, which does not reference mapper Mapper[ParentChildLinkTable(parent_child_link)]

I have the same issue if IO specify "primaryjoin": "ParentChildLinkTable.parent_id==Node.id" and "primaryjoin": "ParentChildLinkTable.child_id==Node.id" under parent/child on ParentChildLinkTable

Option 3

from uuid import UUID, uuid4
from sqlmodel import SQLModel, Field, Relationship

class ParentChildLinkTable(SQLModel, table=True):
    __tablename__ = 'parent_child_link'
    parent_id: UUID = Field(foreign_key="nodes.id", primary_key=True)
    child_id: UUID = Field(foreign_key="nodes.id", primary_key=True)
    parent: 'Node' = Relationship(back_populates="children")
    child: 'Node' = Relationship(back_populates="parents")

class Node(SQLModel, table=True):
    __tablename__ = 'nodes'
    id: UUID = Field(primary_key=True)
    name: str
    parents: list['Node'] = Relationship(back_populates="child", link_model=ParentChildLinkTable)
    children: list['Node'] = Relationship( back_populates="parent", link_model=ParentChildLinkTable)

node_1 = Node(id=uuid4(), name='Node-1')

Results in

AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship ParentChildLinkTable.parent - 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.

This stack-overflow answer provides an example using pure sqlalchemy but I'm unclear how this can be adapted to work with sqlmodel.

Any help would be much appreciated, please let me know if this is better posted in a separate discussion.

[jdwil]

I THINK this works:

from sqlalchemy import Column, UUID as SqlAlchemyUUID, ForeignKey
from sqlmodel import Field, Relationship

_thing1 = Column(SqlAlchemyUUID, ForeignKey('things.id'))
_thing2 = Column(SqlAlchemyUUID, ForeignKey('things.id'))

class Widget(SQLModel, table=True):
    __tablename__ = 'widgets'

    thing_1_id: UUID = Field(sa_column=_thing1)
    thing_2_id: UUID = Field(sa_column=_thing2)

    thing_1: Thing = Relationship(sa_relationship=relationship('Thing', foreign_keys=_thing1))
    thing_2: Thing = Relationship(sa_relationship=relationship('Thing', foreign_keys=_thing2))

[sandstromviktor]

Leaving this for anyone searching for the same issue in the future!
Here is my way of doing it. It looks sort if the same as @jdwil solution above (which i have not tested) but you don't need to use SQLAlchemy stuff directly.

class MyFirstModel(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)

    first: Optional["MySecondModel"] = Relationship(
        back_populates="left",
        sa_relationship_kwargs={"foreign_keys": "MySecondModel.left_id"}
    )
    second: Optional["MySecondModel"] = Relationship(
        back_populates="right",
        sa_relationship_kwargs={"foreign_keys": "MySecondModel.right_id"}
    )

class MySecondModel(SQLModel, table=True):
    id: int | None= Field(default=None, primary_key=True)

    left_id: int | None = Field(default=None, foreign_key="myfirstmodel.id")
    left: MyFirstModel = Relationship(
        back_populates="first",
        sa_relationship_kwargs={"foreign_keys": "MySecondModel.left_id"})


    right_id:  int | None = Field(default=None, foreign_key="myfirstmodel.id")
    right: MyFirstModel = Relationship(
        back_populates="second",
        sa_relationship_kwargs={"foreign_keys": "MySecondModel.right_id"}
    )

[PrinceDobariya0710]

Thanks it worked. Simple and easy approach.