title: Two Columns joining the same table with SqlAlchemy
body: <p>I'm building a sport related application in <strong>Flask</strong> using <strong>SqlAlchemy</strong>. I've got a <em>Game</em> table that has a <code>home_team</code> and an <code>away_team</code> which both join to the <em>Team</em> table.</p>
  
  <p>I want to be able to declare the relationship between the <em>Team</em> table and the <em>Game</em> table but I'm getting an error saying </p>
  
  <blockquote>
    <p><code>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.</code></p>
  </blockquote>
  
  <p>The model declarations look like this:</p>
  
  <pre><code>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):
             ......
  </code></pre>
  
  <p>It looks like I've got to pass the <code>foreign_keys</code> keyword argument to the relationship but I'm not sure how to do that as it needs to be for both <code>away_team_id</code> and <code>home_team_id</code>.</p>
  
  <p>Of course it could also just terrible database design.</p>