Need help creating a one-to-many relationship involving multiple foreign keys

[Lesamuen]

This is in Python 3.14.6 with SQLAlchemy 2.0.50 and using sqlite3 as an engine.

I'm making a data structure for a special online casino where currency is specially tied to the players they originated from.

It involves 3 objects: the games, the players (multiple players per game, but a user can be a part of multiple games at once, hence 2 primary keys), and the chips/currencies (tied to a game, and one player holds chips from another player, hence 3 primary keys).

class Game(SQLBase):
    __tablename__ = "game"

    id: Mapped[int] = mapped_column(primary_key = True)

    players: Mapped[list[Player]] = relationship(back_populates = "game", cascade = "all, delete-orphan")

class Player(SQLBase):
    __tablename__ = "player"

    user_id: Mapped[int] = mapped_column(primary_key = True)
    game_id: Mapped[int] = mapped_column(ForeignKey("game.id", ondelete = "CASCADE"), primary_key = True)

    game: Mapped[Game] = relationship(back_populates = "players")
    chips: Mapped[list[PlayerChips]] = relationship(back_populates = "owner")

class PlayerChips(SQLBase):
    __tablename__ = "chips"

    game_id: Mapped[int] = mapped_column(ForeignKey("game.id", ondelete = "CASCADE"), primary_key = True)
    owner_id: Mapped[int] = mapped_column(ForeignKey("player.user_id", ondelete = "CASCADE"), primary_key = True)
    target_id: Mapped[int] = mapped_column(ForeignKey("player.user_id", ondelete = "CASCADE"), primary_key = True)

    game: Mapped[Game] = relationship()
    owner: Mapped[Player] = relationship(back_populates = "chips", foreign_keys = owner_id)
    target: Mapped[Player] = relationship(foreign_keys = target_id)

The only code that's running is

session.get(Game, id)

The main problem is Player.chips; it keeps giving an AmbiguousForeignKeysError no matter what I do.
AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Player.chips - 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.

I've tried:

    owner: Mapped[Player] = relationship(back_populates = "chips", primaryjoin = "and_(chips.owner_id == player.user_id, chips.game_id == player.game_id)")
    target: Mapped[Player] = relationship(primaryjoin = "and_(chips.target_id == player.user_id, chips.game_id == player.game_id)")

and

    owner: Mapped[Player] = relationship(back_populates = "chips", primaryjoin = "and_(chips.owner_id == player.user_id, chips.game_id == player.game_id)", foreign_keys = owner_id)
    target: Mapped[Player] = relationship(primaryjoin = "and_(chips.target_id == player.user_id, chips.game_id == player.game_id)", foreign_keys = target_id)

Those should be more correct, considering it needs to select for the same game id as well. But they still give me the same error, so the error must be on Player.chips itself.

However, when I try:

    chips: Mapped[list[PlayerChips]] = relationship(back_populates = "owner", foreign_keys = "chips.owner_id")

OR

    chips: Mapped[list[PlayerChips]] = relationship(back_populates = "owner", primaryjoin = "and_(chips.owner_id == player.user_id, chips.game_id == player.game_id)")

they give me a different error:
AttributeError: 'Table' object has no attribute 'owner_id'
and I simply have no idea how to even interpret that, considering I can see owner_id defined right there.
Please help me make sense of this.

[zzzeek]

you want that "chips" relationship to refer to "chips.owner_id" as you saw but you need to refer to it correctly, the most direct way is like this:

   chips: Mapped[list[PlayerChips]] = relationship(back_populates = "owner", foreign_keys=lambda: PlayerChips.owner_id)

these work also:

chips: Mapped[list[PlayerChips]] = relationship(back_populates = "owner", foreign_keys="PlayerChips.owner_id")
chips: Mapped[list[PlayerChips]] = relationship(back_populates = "owner", foreign_keys="chips.c.owner_id")

or even:

chips: Mapped[list[PlayerChips]] = relationship(back_populates = "owner", foreign_keys=lambda: PlayerChips.__table__.c.owner_id)

hope this helps

[Lesamuen]

None of those 4 worked; they all still give me AttributeError: 'Table' object has no attribute 'owner_id' for some unknown reason, even though it is clearly present in the code, and an external extension tells me it's also present in the database itself.

[zzzeek]

here is the model you gave me above illustrated in a complete working program assuming a recent 2.0 series of SQLAlchemy

run this as is and ensure it prints a SELECT statement without error

then try to backtrack into your original code to see what's different

from __future__ import annotations

from sqlalchemy import ForeignKey
from sqlalchemy import select
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
    pass


class Game(Base):
    __tablename__ = "game"

    id: Mapped[int] = mapped_column(primary_key=True)

    players: Mapped[list[Player]] = relationship(
        back_populates="game", cascade="all, delete-orphan"
    )


class Player(Base):
    __tablename__ = "player"

    user_id: Mapped[int] = mapped_column(primary_key=True)
    game_id: Mapped[int] = mapped_column(
        ForeignKey("game.id", ondelete="CASCADE"), primary_key=True
    )

    game: Mapped[Game] = relationship(back_populates="players")
    chips: Mapped[list[PlayerChips]] = relationship(
        back_populates="owner",
        foreign_keys=lambda: PlayerChips.owner_id,
    )


class PlayerChips(Base):
    __tablename__ = "chips"

    game_id: Mapped[int] = mapped_column(
        ForeignKey("game.id", ondelete="CASCADE"), primary_key=True
    )
    owner_id: Mapped[int] = mapped_column(
        ForeignKey("player.user_id", ondelete="CASCADE"), primary_key=True
    )
    target_id: Mapped[int] = mapped_column(
        ForeignKey("player.user_id", ondelete="CASCADE"), primary_key=True
    )

    game: Mapped[Game] = relationship()
    owner: Mapped[Player] = relationship(
        back_populates="chips", foreign_keys=owner_id
    )
    target: Mapped[Player] = relationship(foreign_keys=target_id)


print(select(Player).join(Player.chips))

[Lesamuen]

Okay, it looks like my problem was that after applying your solution, it was erroring due to

    owner: Mapped[Player] = relationship(back_populates = "chips", primaryjoin = "and_(chips.owner_id == player.user_id, chips.game_id == player.game_id)", foreign_keys = owner_id)
    target: Mapped[Player] = relationship(primaryjoin = "and_(chips.target_id == player.user_id, chips.game_id == player.game_id)", foreign_keys = target_id)

Once I removed the primaryjoin stuff, it worked again.

However, now chips will refer to objects of the wrong game if two of the exact same players happen to be both in different games. How do I properly write the primaryjoin statement to get the condition that the games match?

EDIT:
Never mind, I got it. Thank you.

    chips: Mapped[list[PlayerChips]] = relationship(
        back_populates="owner", foreign_keys=lambda: PlayerChips.owner_id,
        primaryjoin = lambda: (Player.user_id == PlayerChips.owner_id) & (Player.game_id == PlayerChips.game_id)
    )

    owner: Mapped[Player] = relationship(
        back_populates="chips", foreign_keys=owner_id,
        primaryjoin = lambda: (PlayerChips.owner_id == Player.user_id) & (PlayerChips.game_id == Player.game_id)
    )
    target: Mapped[Player] = relationship(
        foreign_keys = target_id,
        primaryjoin = lambda: (PlayerChips.target_id == Player.user_id) & (PlayerChips.game_id == Player.game_id)
    )

I'm still curious as to where in the documentation it even mentions the possibility of using lambdas and such. I'm probably just looking in the wrong places.