SQLAlchemy继承关系问题

Question

我的SQLAlchemy模型有一个基类Alpha和两个派生类Beta和Gamma。Beta也是Beta的一个域。我的实现导致下面的消息错误。如何修复此连接条件问题?

AmbiguousForeignKeysError: Could not determine join condition between关系Beta上的父/子表。有多个连接表的外键路径。指定'foreign_keys'参数，提供应被计数为的列的列表包含对父表的外键引用。

class Alpha(declarative_base()):
__tablename__ = 'alpha'
id_ = Column(Integer, primary_key=True)
data = Column(Integer)
type_ = Column(String(50))
__mapper_args__ = {
"polymorphic_identity": "alpha",
"polymorphic_on": type_,
}
class Beta(Alpha):
__tablename__ = 'beta'
id_ = Column(Integer, ForeignKey('alpha.id_'), primary_key=True)
foo = Column(Integer)
# Related
gammas = relationship('Gamma', back_populates='beta')
__mapper_args__ = {
"polymorphic_identity": 'beta',
}
class Gamma(Alpha):
__tablename__ = 'gamma'
id_ = Column(Integer, ForeignKey('alpha.id_'), primary_key=True)
bar = Column(Integer)
beta_id = Column(Integer, ForeignKey('beta.id_'))
beta = relationship('Beta', back_populates='gammas')
__mapper_args__ = {
"polymorphic_identity": 'gamma',
}
declarative_base().metadata.create_all(engine, checkfirst=True)
beta = Beta()

Answer 1

我通过改变两行来解决这个问题:

1. gammas = relationship('Gamma', back_populates='beta', foreign_keys='Gamma.beta_id')
2. beta = relationship('Beta', back_populates='gammas', foreign_keys=[beta_id])

因此，完整的新代码变成:

class Alpha(declarative_base()):
__tablename__ = 'alpha'
id_ = Column(Integer, primary_key=True)
data = Column(Integer)
type_ = Column(String(50))
__mapper_args__ = {
"polymorphic_identity": "alpha",
"polymorphic_on": type_,
}
class Beta(Alpha):
__tablename__ = 'beta'
id_ = Column(Integer, ForeignKey('alpha.id_'), primary_key=True)
foo = Column(Integer)
# Related
gammas = relationship('Gamma', back_populates='beta', foreign_keys='Gamma.beta_id')
__mapper_args__ = {
"polymorphic_identity": 'beta',
}
class Gamma(Alpha):
__tablename__ = 'gamma'
id_ = Column(Integer, ForeignKey('alpha.id_'), primary_key=True)
bar = Column(Integer)
beta_id = Column(Integer, ForeignKey('beta.id_'))
beta = relationship('Beta', back_populates='gammas', foreign_keys=[beta_id])
__mapper_args__ = {
"polymorphic_identity": 'gamma',
}
declarative_base().metadata.create_all(engine, checkfirst=True)
beta = Beta()

更多信息见https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#handling-multiple-join-paths.