帮助处理复杂的SQL炼金术连接

Question

首先，数据库概述：

• competitors-竞争对手
• competitions-人们竞争的东西
• competition_registrations-竞争对手注册了一个特定的比赛
• event-比赛中的“事件”。在
• ^{cd2>一对选手。在

首先，EventCouple，一个与events_couples相对应的Python类是：

class EventCouple(Base):
    __tablename__ = 'events_couples'

    competition_id = Column(Integer, ForeignKey('competitions.id'), primary_key=True)
    event_id = Column(Integer, ForeignKey('events.id'), primary_key=True)

    leader_id = Column(Integer)
    follower_id = Column(Integer)

    __table_args__ = (
        ForeignKeyConstraint(['competition_id', 'leader_id'], ['competition_registrations.competition_id', 'competition_registrations.competitor_id']),
        ForeignKeyConstraint(['competition_id', 'follower_id'], ['competition_registrations.competition_id', 'competition_registrations.competitor_id']),
        {}
    )

我有一个Python类，CompetitorRegistration，它对应于competition_registrations中的一个记录/行。注册的参赛者可以参加多个项目的比赛，但可以是“领队”，也可以是“跟随者”。我想向CompetitorRegistration添加一个属性leading，这是一个EventCouple的列表，其中competition_id和{}匹配。这是我的CompetitorRegistration类，完成时尝试：

^{pr2}$

但是，我得到：

ArgumentError: Could not determine relationship direction for primaryjoin
condition 'CompetitorRegistration.competition_id == EventCouple.competition_id
AND CompetitorRegistration.competitor_id == EventCouple.leader_id', on
relationship CompetitorRegistration.leading. Ensure that the referencing Column
objects have a ForeignKey present, or are otherwise part of a
ForeignKeyConstraint on their parent Table, or specify the foreign_keys parameter
to this relationship.

感谢您的任何帮助，如果需要有关架构的更多信息，请告诉我。在

另外，我的另一个尝试在following中可见-这没有错误，但也没有给出正确的结果。（它只在competition_id上加入，而完全忽略了follower_id）

Answer 1

您的leading的条件将表达式和字符串混合为eval()ed。并且following的条件混合了Python和SQL运算符：and在Python中并不是您所期望的。以下是使用这两种变体的更正示例：

leading = relationship('EventCouple', primaryjoin=(
    (competition_id==EventCouple.competition_id) & \
    (competitor_id==EventCouple.leader_id)))

leading = relationship('EventCouple', primaryjoin=and_(
    competition_id==EventCouple.competition_id,
    competitor_id==EventCouple.leader_id))

following = relationship('EventCouple', primaryjoin=\
    '(CompetitorRegistration.competition_id==EventCouple.competition_id) '\
    '& (CompetitorRegistration.competitor_id==EventCouple.follower_id)')