Sqlalchemy辅助表中的多个外键

Question

我从以下设置开始：

checklist_participants = Table('checklist_participants', base.metadata,
                               Column('checklist_id', Integer, ForeignKey('checklists.id', ondelete='cascade')),
                               Column('user_id', Integer, ForeignKey('users.id', ondelete='cascade'))
                               )

class Checklist(base):
    __tablename__ = 'checklists'

    id = Column(Integer, primary_key=True)
    participants = relationship('User', secondary='checklist_participants', back_populates='joined_checklists')

class User(base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    joined_checklists = relationship('Checklist', secondary='checklist_participants', back_populates='participants')

为了给辅助表referring_user提供一个额外的列checklist_participants，我将其移动到自己的类中，如下所示：

# new class
class Participant(base):
    checklist_id = Column(Integer, ForeignKey('checklists.id', ondelete='cascade'), primary_key=True)
    checklist = relationship('Checklist', foreign_keys=checklist_id)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='cascade'), primary_key=True)
    user = relationship('User', foreign_keys=user_id)
    # new column
    referring_user_id = Column(Integer, ForeignKey('users.id', ondelete='set null'))
    referring_user = relationship('User', foreign_keys=referring_user_id)

class User(base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    joined_checklists = relationship('Checklist', secondary='checklist_participants', back_populates='participants')
    # new relationship definition
    referred_participants = relationship('Participant', back_populates='referring_user')

简单地将联接表移动到它自己的类中不会引起任何问题。但是当我引入新列并尝试运行一个简单的查询时：
session.query(User).filter(User.id == input_id).scalar()
我得到了这个错误：
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Checklist.participants - there are multiple foreign key paths linking the tables via secondary table 'checklist_participants'. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference from the secondary table to each of the parent and child tables.

我需要在模型上定义什么来解决此问题

Answer 1

在@IljaEverilä的帮助下，我意识到了为什么这种关系是模棱两可的。我不知道如何告诉Checklist.participants连接表上的哪个外键是要使用的外键。相反，我从关系中删除了secondary参数，并使Checklist和User关系连接到Participant，而不是直接相互引用。这样，Checklist就不需要知道Participant中使用的外键。
然而，这些更改将问题转移到了User类中，我现在在该类中引用了Participant两次。这一次，我能够在这两个manytone关系上定义foreign_keys参数来修复歧义：

class User(base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    participants = relationship('Participant', back_populates='user', foreign_keys='Participant.user_id')
    referred_participants = relationship('Participant', back_populates='referring_user', foreign_keys='Participant.referring_user_id')


class Checklist(base):
    __tablename__ = 'checklists'

    id = Column(Integer, primary_key=True)
    participants = relationship('Participant', back_populates='checklist')

class Participant(base):
    __tablename__ = 'checklist_participants'

    checklist_id = Column(Integer, ForeignKey('checklists.id', ondelete='cascade'), primary_key=True)
    checklist = relationship('Checklist', foreign_keys=checklist_id)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='cascade'), primary_key=True)
    user = relationship('User', foreign_keys=user_id)
    referring_user_id = Column(Integer, ForeignKey('users.id', ondelete='set null'))
    referring_user = relationship('User', foreign_keys=referring_user_id)