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 | +-------+---------+-------+---------+
Advertisement
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()