6

Hi I'm using flask and sqlalchemy, I'm trying to get the matches relationship in Team to get all matches whether or not it is team1 or team2 (so what i want is to be able to get all matches for given team through the matches attribute regardless if it is team1 or team2 in the Match table), I get the error:

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Team.matches

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.

class Match(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  map = db.Column(db.String(80), index=True)
  date = db.Column(db.DateTime, index=True)
  agreed = db.Column(db.Boolean)
  done = db.Column(db.Boolean)
  ladder_id = db.Column(db.Integer, db.ForeignKey('ladder.id'))
  team1_id = db.Column(db.Integer, db.ForeignKey('team.id'))
  team2_id = db.Column(db.Integer, db.ForeignKey('team.id'))
  team1_rounds = db.Column(db.Integer)
  team2_rounds = db.Column(db.Integer)
  team1_accepted_score = db.Column(db.Boolean)
  team2_accepted_score = db.Column(db.Boolean)
  points = db.Column(db.Integer)

  team1 = db.relationship('Team', foreign_keys='Match.team1_id')
  team2 = db.relationship('Team', foreign_keys='Match.team2_id')

class Team(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String(80), index=True)
  tag = db.Column(db.String(20), index=True, unique=True)
  captain_id = db.Column(db.Integer, db.ForeignKey('user.id'))
  captain = db.relationship('User', uselist=False,
               foreign_keys='Team.captain_id')
  members = db.relationship('User', backref='team',
                foreign_keys='User.team_id', lazy='dynamic')
  matches = db.relationship('Match',
                 foreign_keys='[Match.team1_id, Match.team2_id]', lazy='dynamic')

1 Answer 1

10

I got help from the guys on #sqlalchemy irc so i was going with the wrong approach I've now set up a relationship with a primaryjoin instead:

matches = db.relationship('Match', primaryjoin="or_(Team.id==Match.team1_id, Team.id==Match.team2_id)", lazy='dynamic')
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.