Two Foreign Keys with the same Primary Key

[laynef]

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 sqlmodel import SQLModel, Field, Relationship
from typing import Optional, List

class User(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, index=True)
    phone_number: str = Field(unique=True, index=True)
    hashed_password: str
    two_auth_code: str
    is_active: bool = Field(default=True)

    user_stocks: List["Stock"] = Relationship(back_populates="stock_user")
    user_bank_accounts: List["BankAccount"] = Relationship(back_populates="bank_account_user")
    user_bank_transactions: List["BankTransaction"] = Relationship(back_populates="bank_transaction_user")
    user_app_transactions: List["AppTransaction"] = Relationship(back_populates="app_transaction_user")
    user_portfolio: Optional["Portfolio"] = Relationship(back_populates="portfolio_user")
    user_goals: List["Goal"] = Relationship(back_populates="goal_user")

class Portfolio(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, index=True)
    ratio: float

    portfolio_user_id: Optional[int] = Field(default=None, foreign_key="user.id")

    portfolio_user: Optional[User] = Relationship(back_populates="user_portfolio")
    portfolio_stocks: List["Stock"] = Relationship(back_populates="stock_portfolio")

class Stock(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, index=True)
    symbol: str
    name: str
    description: str
    price: float

    stock_user_id: Optional[int] = Field(default=None, foreign_key="user.id")
    stock_portfolio_id: Optional[int] = Field(default=None, foreign_key="portfolio.id")

    stock_user: Optional[User] = Relationship(back_populates="user_stocks")
    stock_portfolio: Optional[Portfolio] = Relationship(back_populates="portfolio_stocks")

class BankAccount(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, index=True)
    amount: float
    name: str
    description: str

    bank_account_user_id: Optional[int] = Field(default=None, foreign_key="user.id")

    bank_account_user: Optional[User] = Relationship(back_populates="user_bank_accounts")
    bank_account_to_bank_transactions: List["BankTransaction"] = Relationship(back_populates="bank_transaction_to_bank_account")
    bank_account_from_bank_transactions: List["BankTransaction"] = Relationship(back_populates="bank_transaction_from_bank_account")
    bank_account_to_app_transactions: List["AppTransaction"] = Relationship(back_populates="app_transaction_to_bank_account")
    bank_account_from_app_transactions: List["AppTransaction"] = Relationship(back_populates="app_transaction_from_bank_account")

class BankTransaction(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, index=True)
    amount: float
    name: str
    description: str
    is_pending: bool

    bank_transaction_user_id: Optional[int] = Field(default=None, foreign_key="user.id")
    bank_transaction_to_bank_account_id: Optional[int] = Field(default=None, foreign_key="bankaccount.id")
    bank_transaction_from_bank_account_id: Optional[int] = Field(default=None, foreign_key="bankaccount.id")

    bank_transaction_user: Optional[User] = Relationship(back_populates="user_bank_transactions")
    bank_transaction_to_bank_account: Optional[BankAccount] = Relationship(back_populates="bank_account_to_bank_transactions")
    bank_transaction_from_bank_account: Optional[BankAccount] = Relationship(back_populates="bank_account_from_bank_transactions")

class AppTransaction(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, index=True)
    amount: float
    name: str
    description: str
    is_pending: bool

    app_transaction_user_id: Optional[int] = Field(default=None, foreign_key="user.id")
    app_transaction_to_bank_account_id: Optional[int] = Field(default=None, foreign_key="bankaccount.id")
    app_transaction_from_bank_account_id: Optional[int] = Field(default=None, foreign_key="bankaccount.id")

    app_transaction_user: Optional[User] = Relationship(back_populates="user_app_transactions")
    app_transaction_to_bank_account: Optional[BankAccount] = Relationship(back_populates="bank_account_to_app_transactions")
    app_transaction_from_bank_account: Optional[BankAccount] = Relationship(back_populates="bank_account_from_app_transactions")

class Goal(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, index=True)
    amount: float
    goal_amount: float
    name: str
    description: str

    goal_user_id: Optional[int] = Field(default=None, foreign_key="user.id")
    goal_user: Optional[User] = Relationship(back_populates="user_goals")

Description

I am trying to link 2 BankAccount IDs on a single table (i.e. BankTransaction & AppTransaction)

How do I properly link a bank account ID twice on a single table?

I am currently getting this error:

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

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

Python 3.11.5

Additional Context

No response

[antont]

Seems to be solved in https://stackoverflow.com/questions/26965983/sqlalchemy-ambiguousforeignkeyserror

relationship(User_address, backref='user',
                             primaryjoin=
                                id==User_address.user_id
                            )

you can pass that primaryjoin param by using sqlmodel relationship sa_kwargs like there, https://stackoverflow.com/questions/71464757/what-does-sa-relationship-kwargs-lazy-selectin-means-on-sqlmodel-with-f

sa_relationship_kwargs={"lazy": "selectin"}

[laynef]

sa_relationship_kwargs={"lazy": "selectin"}

Doesn't seem to work

[antont]

that was just an example to show how sa_relationship_kwargs works, i.e you can use it to pass the primaryjoin param.

apparently is possible with your foreign_keys fix too.

[laynef]

I fixed it with this:

class BankTransaction(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, index=True)
    amount: float
    name: str
    description: str
    is_pending: bool

    bank_transaction_user_id: Optional[int] = Field(default=None, foreign_key="user.id")
    bank_transaction_to_bank_account_id: Optional[int] = Field(default=None, foreign_key="bankaccount.id")
    bank_transaction_from_bank_account_id: Optional[int] = Field(default=None, foreign_key="bankaccount.id")

    bank_transaction_user: Optional[User] = Relationship(back_populates="user_bank_transactions")
    bank_transaction_to_bank_account: Optional["BankAccount"] = Relationship(back_populates="bank_account_to_bank_transactions", sa_relationship=RelationshipProperty("BankAccount", foreign_keys=[bank_transaction_to_bank_account_id]))
    bank_transaction_from_bank_account: Optional["BankAccount"] = Relationship(back_populates="bank_account_from_bank_transactions", sa_relationship=RelationshipProperty("BankAccount", foreign_keys=[bank_transaction_to_bank_account_id]))

class AppTransaction(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, index=True)
    amount: float
    name: str
    description: str
    is_pending: bool

    app_transaction_user_id: Optional[int] = Field(default=None, foreign_key="user.id")
    app_transaction_to_bank_account_id: Optional[int] = Field(default=None, foreign_key="bankaccount.id")
    app_transaction_from_bank_account_id: Optional[int] = Field(default=None, foreign_key="bankaccount.id")

    app_transaction_user: Optional[User] = Relationship(back_populates="user_app_transactions")
    app_transaction_to_bank_account: Optional["BankAccount"] = Relationship(back_populates="bank_account_to_app_transactions", sa_relationship=RelationshipProperty("BankAccount", foreign_keys=[app_transaction_to_bank_account_id]))
    app_transaction_from_bank_account: Optional["BankAccount"] = Relationship(back_populates="bank_account_from_app_transactions", sa_relationship=RelationshipProperty("BankAccount", foreign_keys=[app_transaction_from_bank_account_id]))

class BankAccount(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, index=True)
    amount: float
    name: str
    description: str

    bank_account_user_id: Optional[int] = Field(default=None, foreign_key="user.id")

    bank_account_user: Optional[User] = Relationship(back_populates="user_bank_accounts")
    bank_account_to_bank_transactions: List["BankTransaction"] = Relationship(back_populates="bank_transaction_to_bank_account", sa_relationship=RelationshipProperty("BankTransaction", foreign_keys=["bank_transaction_to_bank_account_id"]))
    bank_account_from_bank_transactions: List["BankTransaction"] = Relationship(back_populates="bank_transaction_from_bank_account", sa_relationship=RelationshipProperty("BankTransaction", foreign_keys=["bank_transaction_from_bank_account_id"]))
    bank_account_to_app_transactions: List["AppTransaction"] = Relationship(back_populates="app_transaction_to_bank_account", sa_relationship=RelationshipProperty("BankTransaction", foreign_keys=["app_transaction_to_bank_account_id"]))
    bank_account_from_app_transactions: List["AppTransaction"] = Relationship(back_populates="app_transaction_from_bank_account", sa_relationship=RelationshipProperty("BankTransaction", foreign_keys=["app_transaction_from_bank_account_id"]))

[jd-solanki]

What did you change?