How to use back_populates() in SQLAlchemy with two (or more) foreign keys from same table?

Question

I have three tables: Competitor, Competition and Duel.

class Competitor(db.Model):  # type: ignore
    __tablename__ = 'competitors'

    id_competitor = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(255), nullable=False)


class Duel(db.Model):  # type: ignore
    __tablename__ = 'duels'

    id_competitor1 = db.Column(db.ForeignKey('competitors.id_competitor'), primary_key=True)
    id_competitor2 = db.Column(db.ForeignKey('competitors.id_competitor'), primary_key=True)
    id_competition = db.Column(db.ForeignKey('competitions.id_competition'), primary_key=True)
    phase = db.Column(db.Integer, primary_key=True)

    rel_competition = relationship('Competition', back_populates='rel_competition_duel')
    rel_competitor1 = relationship('Competitor', foreign_keys=[id_competitor1])
    rel_competitor2 = relationship('Competitor', foreign_keys=[id_competitor2])


class Competition(db.Model):  # type: ignore
    __tablename__ = 'competitions'

    id_competition = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(255), nullable=False)

    rel_competition_duel = relationship('Duel', back_populates='rel_competition')

As one can see, there are two foreign keys in Duel that reference table Competitor. This is what works.

However, I want to connect relationships with back_populates() so that modifying one also modifies the other.

If I replace two relationships in Duel with these two:

rel_competitor1 = relationship('Competitor', back_populates='rel_duel1')
rel_competitor2 = relationship('Competitor', back_populates='rel_duel2')

and add:

rel_duel1 = relationship('Duel', back_populates='rel_competitor1')
rel_duel2 = relationship('Duel', back_populates='rel_competitor2')

to Competitor, I get the error: "[..] Original exception was: Could not determine join condition between parent/child tables on relationship Competitor.rel_duel1 - 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."

So, how do I connect relationships with back_populates() when I have two (or possibly more) foreign keys from same table?

Answer 1

After some thinking, I added foreign keys to Duel, but it still produced an error. So after some reading and searching I realized that I need to add foreign keys to both relationships, in Duel and in Competitor.

So, final solution is to add:

rel_duel1 = relationship('Duel', back_populates='rel_competitor1', foreign_keys='Duel.id_competitor1')
rel_duel2 = relationship('Duel', back_populates='rel_competitor2', foreign_keys='Duel.id_competitor2')

to Competitor, and:

rel_competitor1 = relationship('Competitor', back_populates='rel_duel1', foreign_keys=[id_competitor1])
rel_competitor2 = relationship('Competitor', back_populates='rel_duel2', foreign_keys=[id_competitor2])

to Duel. Now it works.