How Do I Get Multiple Foreign Keys Targetting The Same Model? (AmbiguousForeignKeysError)

I have two straightforward models like so class GameModel(db.Model): __tablename__ = 'games' id = db.Column(db.Integer, primary_key=True) home_team = db.Column(db.Inte

Solution 1:

You'll need to maintain separate lists of home_games and away_games and then combine them to return the full list of games:

import datetime

import sqlalchemy as sa
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import declarative_base, relationship

connection_uri = (
    "mssql+pyodbc://@localhost:49242/myDb?driver=ODBC+Driver+17+for+SQL+Server"
)
engine = sa.create_engine(
    connection_uri,
    future=True,
    echo=False,
)

Base = declarative_base()


class Game(Base):
    __tablename__ = "game"
    id = sa.Column(sa.Integer, primary_key=True)
    when = sa.Column(sa.Date, nullable=False)
    home_team_id = sa.Column(sa.Integer, sa.ForeignKey("team.id"))
    away_team_id = sa.Column(sa.Integer, sa.ForeignKey("team.id"))
    home_team = relationship(
        "Team", foreign_keys=[home_team_id], back_populates="home_games"
    )
    away_team = relationship(
        "Team", foreign_keys=[away_team_id], back_populates="away_games"
    )

    def __repr__(self):
        return f"<Game(when='{self.when}')>"


class Team(Base):
    __tablename__ = "team"
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(50))
    home_games = relationship(
        Game, foreign_keys=[Game.home_team_id], back_populates="home_team"
    )
    away_games = relationship(
        Game, foreign_keys=[Game.away_team_id], back_populates="away_team"
    )

    @hybrid_property
    def games(self):
        return self.home_games + self.away_games

    def __repr__(self):
        return f"<Team(name='{self.name}')>"


# <just for testing>
Base.metadata.drop_all(engine, checkfirst=True)
Base.metadata.create_all(engine)
# </just for testing>

with sa.orm.Session(engine, future=True) as session:
    t_a = Team(name="Team_A")
    t_b = Team(name="Team_B")
    g1 = Game(when=datetime.date(2021, 1, 1), home_team=t_a, away_team=t_b)
    g2 = Game(when=datetime.date(2022, 2, 2), home_team=t_b, away_team=t_a)
    session.add_all([t_a, t_b, g1, g2])
    print(t_a.home_games)  # [<Game(when='2021-01-01')>]
    print(t_a.away_games)  # [<Game(when='2022-02-02')>]
    print(t_a.games)  # [<Game(when='2021-01-01')>, <Game(when='2022-02-02')>]