与炼金术的分支关系

Question

我有一个有点分支的关系结构（让我们用一个配对数据库的例子-不幸的是，实际情况有点复杂）：

class Hobby(Base):
    __tablename__ = 'hobby'
    id = Column(Integer, primary_key=True)
    hobby_name = Column(String(255, u'utf8_unicode_ci'), nullable=False)

class Guy(Base):
    __tablename__ = 'guy'
    id = Column(Integer, primary_key=True)
    name = Column(String(255, u'utf8_unicode_ci'), nullable=False)

class Girl(Base):
    __tablename__ = 'girl'
    id = Column(Integer, primary_key=True)
    name = Column(String(255, u'utf8_unicode_ci'), nullable=False)

class Match(Base):
    __tablename__ = 'match'
    id = Column(Integer, primary_key=True)
    guy_id = Column(ForeignKey(u'guy.id'), nullable=False)
    girl_id = Column(ForeignKey(u'girl.id'), nullable=False)

    guy = relationship(u'Guy', backref = 'matches')
    girl = relationship(u'Girl', backref = 'matches')

class GuyHobbies(Base):
    __tablename__ = 'guy_hobbies'
    id = Column(Integer, primary_key=True)
    guy_id = Column(ForeignKey(u'guy.id'), nullable=False)
    hobby_id = Column(ForeignKey(u'hobby.id'), nullable=False)

    guy = relationship(u'Guy', backref = 'hobbies')
    hobby = relationship(u'Hobby', backref = 'guys')

class GirlHobbies(Base):
    __tablename__ = 'girl_hobbies'
    id = Column(Integer, primary_key=True)
    girl_id = Column(ForeignKey(u'girl.id'), nullable=False)
    hobby_id = Column(ForeignKey(u'hobby.id'), nullable=False)

    girl = relationship(u'Girl', backref = 'hobbies')
    hobby = relationship(u'Hobby', backref = 'girls')

我现在想在GirlHobby和GuyHobby之间建立一个连接，它考虑了通过Hobby和通过Match建立的关系。也就是说，我正在做一个

matched_hobbies = session.query(GuyHobbies).join(GuyHobbies.guy).\
    join(GuyHobbies.hobby).join(Guy.matches).\
    join(Match.girl).outerjoin(Girl.hobbies).all()

但是，生成的查询缺少下面标记的部分。如何让SQLAlchemy添加此条件？你知道吗

SELECT guy_hobbies.id AS guy_hobbies_id, guy_hobbies.guy_id AS guy_hobbies_guy_id, 
    guy_hobbies.hobby_id AS guy_hobbies_hobby_id 
FROM guy_hobbies INNER JOIN guy ON guy.id = guy_hobbies.guy_id 
    INNER JOIN hobby ON hobby.id = guy_hobbies.hobby_id 
    INNER JOIN `match` ON guy.id = `match`.guy_id 
    INNER JOIN girl ON girl.id = `match`.girl_id 
    LEFT OUTER JOIN girl_hobbies ON girl.id = girl_hobbies.girl_id 
        #MISSING: AND hobby.id = girl_hobbies.hobby_id

另外：我试图建立一个直接的关系，男性嗜好和女孩之间的使用

girl_hobbies = relationship('GirlHobbies', 
    primaryjoin ="and_(GuyHobbies.guy_id == Guy.id, 
    Match.guy_id == Guy.id ,Match.girl_id == Girl.id, 
    GirlHobbies.girl_id == Girl.id , 
    GirlHobbies.hobby_id == Hobby.id, 
    GuyHobbies.hobby_id == Hobby.id)")

但我得到了一个颇具讽刺意味的错误信息：

Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'guy_hobbies.guy_id = guy.id AND match.guy_id = guy.id AND match.girl_id = girl.id AND girl_hobbies.girl_id = girl.id AND girl_hobbies.hobby_id = hobby.id AND guy_hobbies.hobby_id = hobby.id' on relationship GuyHobbies.girl_hobbies.

它正好显示了我希望SQLAlchemy使用的连接条件。。。你知道吗

Answer 1

由于所有的积分都在SQLAlchemy IRC通道的@inklesspen中，连接条件必须直接在连接中指定，即

matched_hobbies = session.query(GuyHobbies).join(GuyHobbies.guy).\
    join(GuyHobbies.hobby).join(Guy.matches).\
    join(Match.girl).\
    outerjoin(GirlHobbies, 
       and_(GirlHobbies.hobby_id == GuyHobbies.hobby_id,
            GirlHobbies.girl_id == Girl.id)).all()