CODEWITHSUNDEEP
pythonflasksqlalchemy
Sander bakker
Sander bakker

Reputation: 505

Could not determine join condition between parent/child tables, setting foreign_keys

class Team(db.Model):
    __tablename__ = "Teams"
    id = db.Column(db.Integer, primary_key=True)
    flashscore_id = db.Column(db.String(255), nullable=False, unique=True)
    name = db.Column(db.String(255), nullable=False)

    leagues = db.relationship("League",
                    secondary=league_teams_table)

    standings = db.relationship('Standing', backref='teams', cascade="all,delete")
    fixtures = db.relationship('Fixture', backref='teams', cascade="all,delete")

related_fixtures_table = db.Table('RelatedFixtures',
    db.Column('fixture_id', db.Integer, db.ForeignKey('Fixtures.id')),
    db.Column('related_fixture_id', db.Integer, db.ForeignKey('Fixtures.id')))

class Fixture(db.Model):
    __tablename__ = "Fixtures"
    id = db.Column(db.Integer, primary_key=True)

    home_id = db.Column(db.Integer, db.ForeignKey('Teams.id'),
        nullable=False)
    away_id = db.Column(db.Integer, db.ForeignKey('Teams.id'),
        nullable=False)

    home_ref = db.relationship("Teams", backref="fixture", uselist=False, foreign_keys=[home_id])
    away_ref = db.relationship("Teams", backref="fixture", uselist=False, foreign_keys=[away_id])

    flashscore_id = db.Column(db.String(255), nullable=False, unique=True)
    total_goals = db.Column(db.Integer, nullable=False)

    total_fh_goals = db.Column(db.Integer, nullable=False, default=0)
    total_sh_goals = db.Column(db.Integer, nullable=False, default=0)

    total_home_goals = db.Column(db.Integer, nullable=False)
    total_away_goals = db.Column(db.Integer, nullable=False)

    total_home_fh_goals = db.Column(db.Integer, nullable=False, default=0)
    total_home_sh_goals = db.Column(db.Integer, nullable=False, default=0)

    total_away_fh_goals = db.Column(db.Integer, nullable=False, default=0)
    total_away_sh_goals = db.Column(db.Integer, nullable=False, default=0)

    related_fixtures = db.relationship("Fixture",
                    secondary=related_fixtures_table)

I wrote the above code trying to define multiple relations between Fixture & Team. When I run the application and execute an action I get the following error: 

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Team.fixtures - 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 tried solving this by adding the following two lines:

home_ref = db.relationship("Teams", backref="fixture", uselist=False, foreign_keys=[home_id])
    away_ref = db.relationship("Teams", backref="fixture", uselist=False, foreign_keys=[away_id])

But I still get the same error. Can anyone help me out?

Upvotes: 7

Views: 8586

Answers (2)

Gitau Harrison
Gitau Harrison

Reputation: 3517

You need to get rid of fixtures = db.relationship('Fixture', backref='teams', cascade="all,delete") in Team.

What you will have at the end is:

class Team(db.Model):
    __tablename__ = "Teams"
    id = db.Column(db.Integer, primary_key=True)
    
    # ...
    

class Fixture(db.Model):
    __tablename__ = "Fixtures"
    id = db.Column(db.Integer, primary_key=True)

    home_id = db.Column(db.Integer, db.ForeignKey('Teams.id'), nullable=False)
    away_id = db.Column(db.Integer, db.ForeignKey('Teams.id'), nullable=False)

    home_ref = db.relationship("Teams", backref="fixture", uselist=False, foreign_keys=[home_id])
    away_ref = db.relationship("Teams", backref="fixture", uselist=False, foreign_keys=[away_id])
    
    # ...

You have your foreign keys defined in home_id and away_id, then you 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. Refer to this section in the SQLAlchemy documentation.

Upvotes: 2

Martijn Pieters
Martijn Pieters

Reputation: 1123400

You need to configure the fixtures relationship on the Team class, because that's what the error is telling you to do: Could not determine join condition ... on relationship Team.fixtures.

However, you can't fix this situation with foreign_keys; that expects a single (possibly compound) foreign key, and you have two, distinct foreign key relationships. You'd have to create two separate relationships, one for fixtures where this team is the home team, and another one for the away relationship.

If you want Team.fixtures to return a list of all Fixture rows that reference the team via one or both foreign keys, you need to create a custom primaryjoin condition, one that matches your Team.id against the home_id or the away_id column (and you probably want to add a DISTINCT condition to your query to avoid getting duplicate results for fixtures that connect to your team via both foreign keys).

Upvotes: 2

Related Questions