Why am I getting AmbiguousForeignKeysError?

I’ve run into an issue after following the SqlAlchemy guide here.

Given the following simplified module:

class _Base():
    id_ = Column(Integer, primary_key=True, autoincrement=True)


Base = declarative_base(cls=_Base)


class BlgMixin():

    @declared_attr
    def __table_args__(cls):
        return {'schema': "belgarath_backup", "extend_existing": True}


class DataAccessLayer():

    def __init__(self):
        conn_string = "mysql+mysqlconnector://root:root@localhost/"
        self.engine = create_engine(conn_string)

    def create_session(self):
        Base.metadata.create_all(self.engine)
        Session = sessionmaker()
        Session.configure(bind=self.engine)
        self.session = Session()


class Player(Base, BlgMixin):
    __tablename__ = "player"

    name_ = Column(String(100))

    match = relationship("MatchResult")


class MatchResult(Base, BlgMixin):
    __tablename__ = "match_result"

    p1_id = Column(Integer, ForeignKey(f"{BlgMixin.__table_args__.get('schema')}.player.id_"))
    p2_id = Column(Integer, ForeignKey(f"{BlgMixin.__table_args__.get('schema')}.player.id_"))

    p1 = relationship("Player", foreign_keys=f"{BlgMixin.__table_args__.get('schema')}.player.id_")
    p2 = relationship("Player", foreign_keys=f"{BlgMixin.__table_args__.get('schema')}.player.id_")

That I am attempting to build a query using:

dal = DataAccessLayer()
dal.create_session()

player_1 = aliased(Player)
player_2 = aliased(Player)

matches = dal.session.query(MatchResult.p1_id, player_1.name_, MatchResult.p2_id, player_2.name_)
matches = matches.join(player_1)
matches = matches.join(player_2)

Why am I getting the following error?

Could not determine join condition between parent/child tables on relationship Player.match - 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 was pretty sure I’d specified the two foreign key relationships?

---

Update:

I’ve tried the following combination as I think has been suggested in the comments but got the same error:

p1 = relationship("Player", foreign_keys=[p1_id])
p2 = relationship("Player", foreign_keys=[p2_id])

---

Update 2:

Added some details on what the output should look like:

player table:

+-----+-------+
| id_ | name_ |
+-----+-------+
|   1 | foo   |
|   2 | bar   |
|   3 | baz   |
|   4 | zoo   |
+-----+-------+

match_result table:

+-----+-------+-------+
| id_ | p1_id | p2_id |
+-----+-------+-------+
|   1 |     1 |     2 |
|   2 |     2 |     1 |
|   3 |     3 |     1 |
|   4 |     1 |     4 |
+-----+-------+-------+

Query output:

+-------+---------+-------+---------+
| p1_id | p1_name | p2_id | p2_name |
+-------+---------+-------+---------+
|     1 | foo     |     2 | bar     |
|     2 | bar     |     1 | foo     |
|     3 | baz     |     1 | foo     |
|     1 | foo     |     4 | zoo     |
+-------+---------+-------+---------+

Answer

The two-way relationship and multiple join paths prevent SQLAlchemy from automatically determining the joins, and the relationships in both tables emit very similar error messages makes it difficult to understand where the problems lie (and whether a given change makes any progress in solving them). I found the simplest approach was to comment out the relationship in Player until MatchResult was working properly.

The changes to MatchResult are the same as those specified in the multiple join paths docs referenced in the question. To get the relationship in Player to work I specified the primary join condition so that SQLAlchemy could determine how to join to MatchResult.

class Player(Base):
    __tablename__ = 'player'

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(100))

    matches = orm.relationship('MatchResult',
       primaryjoin="or_(Player.id == MatchResult.p1_id, Player.id == MatchResult.p2_id)")


class MatchResult(Base):
    __tablename__ = 'match_result'

    id = sa.Column(sa.Integer, primary_key=True)
    p1_id = sa.Column(sa.Integer, sa.ForeignKey('player.id'))
    p2_id = sa.Column(sa.Integer, sa.ForeignKey('player.id'))

    p1 = orm.relationship("Player", foreign_keys=[p1_id])
    p2 = orm.relationship("Player", foreign_keys=[p2_id])

Once these changes have been made, basic querying can be done without any explicit aliasing or joins.

ms = session.query(MatchResult)
for r in ms:
    print(r.p1_id, r.p1.name, r.p2_id, r.p2.name)

p1 = session.query(Player).filter(Player.name == 'bar').one()
for m in p1.matches:
    print(m.p1.name, m.p2.name)

---

The above code, for clarity and usefulness to other readers, does not include the inheritance, mixin and session management code that is specific to the OP’s application. Thiis version includes all of these.

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy import orm


class _Base():
    id_ = sa.Column(sa.Integer, primary_key=True, autoincrement=True)


Base = declarative_base(cls=_Base)


class BlgMixin():

    @declared_attr
    def __table_args__(cls):
        return {'schema': "belgarath_backup", "extend_existing": True}


class DataAccessLayer():

    def __init__(self):
        conn_string = "mysql+mysqlconnector://root:root@localhost/"
        self.engine = sa.create_engine(conn_string)

    def create_session(self):
        Base.metadata.create_all(self.engine)
        Session = orm.sessionmaker()
        Session.configure(bind=self.engine)
        self.session = Session()


class Player(Base, BlgMixin):
    __tablename__ = 'player'

    name = sa.Column(sa.String(100))

    match = orm.relationship('MatchResult',
                             primaryjoin="or_(Player.id_ == MatchResult.p1_id, Player.id_ == MatchResult.p2_id)")


class MatchResult(Base, BlgMixin):
    __tablename__ = 'match_result'

    p1_id = sa.Column(sa.Integer, sa.ForeignKey(f'{BlgMixin.__table_args__.get("schema")}.player.id_'))
    p2_id = sa.Column(sa.Integer, sa.ForeignKey(f'{BlgMixin.__table_args__.get("schema")}.player.id_'))

    p1 = orm.relationship("Player", foreign_keys=[p1_id])
    p2 = orm.relationship("Player", foreign_keys=[p2_id])


dal = DataAccessLayer()
Base.metadata.drop_all(bind=dal.engine)
Base.metadata.create_all(bind=dal.engine)


names = ['foo', 'bar', 'baz', 'zoo']
dal.create_session()
ps = [Player(name=n) for n in names]
dal.session.add_all(ps)
dal.session.flush()
p1, p2, p3, p4 = ps
m1 = MatchResult(p1_id=p1.id_, p2_id=p2.id_)
m2 = MatchResult(p1_id=p2.id_, p2_id=p1.id_)
m3 = MatchResult(p1_id=p3.id_, p2_id=p1.id_)
m4 = MatchResult(p1_id=p1.id_, p2_id=p4.id_)
dal.session.add_all([m1, m2, m3, m4])
dal.session.commit()


ms = dal.session.query(MatchResult)
for r in ms:
    print(r.p1_id, r.p1.name, r.p2_id, r.p2.name)
print()

p1 = dal.session.query(Player).filter(Player.name == 'bar').one()
for m in p1.match:
    print(m.p1.name, m.p2.name)
dal.session.close()