Friday, 20 November 2020

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     |
+-------+---------+-------+---------+


from Why am I getting AmbiguousForeignKeysError?

No comments:

Post a Comment