Two Columns joining the same table with SqlAlchemy

Question

I'm building a sport related application in Flask using SqlAlchemy. I've got a Game table that has a home_team and an away_team which both join to the Team table.

I want to be able to declare the relationship between the Team table and the Game table but I'm getting an error saying

Could not determine join condition between parent/child tables on relationship Team.games - 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 model declarations look like this:

class Team(db.Model):

    """Team model."""

    __tablename__ = "teams"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    monkier = db.Column(db.String)
    town = db.Column(db.String)

    games = db.relationship('Game')

    def __init__(self):
           ......

class Game(db.Model):

    """Game model."""

    __tablename__ = "games"

    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.DateTime)
    ground_id = db.Column(db.Integer, db.ForeignKey('grounds.id'))
    round_id = db.Column(db.Integer, db.ForeignKey('rounds.id'))
    home_team_id = db.Column(db.Integer, db.ForeignKey('teams.id'))
    away_team_id = db.Column(db.Integer, db.ForeignKey('teams.id'))
    home_score = db.Column(db.String)
    away_score = db.Column(db.String)

    ground = db.relationship('Ground')
    round = db.relationship('Round')
    home_team = db.relationship('Team',
                                primaryjoin="Game.home_team_id == Team.id")
    away_team = db.relationship('Team',
                                primaryjoin="Game.away_team_id == Team.id")

    def __init__(self):
           ......

It looks like I've got to pass the foreign_keys keyword argument to the relationship but I'm not sure how to do that as it needs to be for both away_team_id and home_team_id.

Of course it could also just terrible database design.

Answer 1

I think you create the relationships you're looking for by making a few tweaks to your models.

class Team(db.Model):

    """Team model."""

    __tablename__ = "teams"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    monkier = db.Column(db.String)
    town = db.Column(db.String)

    home_games = db.relationship('Game', foreign_keys='Game.home_team_id', backref='home_team', lazy='dynamic')
    away_games = db.relationship('Game', foreign_keys='Game.away_team_id', backref='away_team', lazy='dynamic')

    def __init__(self):
           ......

    @property
    def games(self):
        return self.home_games.union(self.away_games)

class Game(db.Model):

    """Game model."""

    __tablename__ = "games"

    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.DateTime)
    ground_id = db.Column(db.Integer, db.ForeignKey('grounds.id'))
    round_id = db.Column(db.Integer, db.ForeignKey('rounds.id'))
    home_team_id = db.Column(db.Integer, db.ForeignKey('teams.id'))
    away_team_id = db.Column(db.Integer, db.ForeignKey('teams.id'))
    home_score = db.Column(db.String)
    away_score = db.Column(db.String)

    ground = db.relationship('Ground')
    round = db.relationship('Round')

    def __init__(self):
           ......

Now you can access the Team model through the backref property defined in the relationships of Team.

e.g. game.away_team or game.home_team

You can also query specifically home, away, or all games specific to a team.

all_home_games = team.home_games.all()
away_against_dodgers = team.away_games.filter_by(name='Dodgers').all()
some_date = DateTime(...)
all_games_before_date = team.games.filter(Game.date < some_date).all()