使用内连接和外连接创建查询时出现问题

Question

我有以下模型（简化）：

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)

class Thing(Base):
    __tablename__ = 'thing'
    id = Column(Integer, primary_key=True)

class Relationship(Base):
    __tablename__ = 'relationship'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('thing.id'))
    parent = relationship('Thing', backref='parentrelationships', primaryjoin = "Relationship.parent_id == Thing.id")
    child_id = Column(Integer, ForeignKey('thing.id'))
    child = relationship('Thing', backref='childrelationships', primaryjoin = "Relationship.child_id == Thing.id")

class Vote(Base)
    __tablename__ = 'vote'
    id = Column(Integer, primary_key=True)
    rel_id = Column(Integer, ForeignKey('relationship.id'))
    rel = relationship('Relationship', backref='votes')
    voter_id = Column(Integer, ForeignKey('user.id'))
    voter = relationship('User', backref='votes')

我想查询与某个父级的所有关系，并且我还想查询某个用户对这些关系所做的投票。我尝试过的：

def get_relationships(thisthing, thisuser):
    return DBSession.query(Relationship, Vote).\
        filter(Relationship.parent_id == thisthing.id).\
        outerjoin(Vote, Relationship.id == Vote.rel_id).\
        filter(Vote.voter_id == thisuser.id).\
        filter(Vote.rel_id == Relationship.id).\
        all()

以及：

def get_relationships(thisthing, thisuser):
    session = DBSession()
    rels = session.query(Relationship).\
        filter(Relationship.parent_id == thisthing.id).\ 
        subquery()
    return session.query(rels, Vote).\
        outerjoin(Vote, rels.c.id == Vote.rel_id).\
        filter(Vote.voter_id == thisuser.id).\
        all()

当我执行这些查询中的任何一个时，我都会得到空值。我做错了什么？

Answer 1

只需打开 SQL 日志记录 (echo=True)，您就会看到第一个选项的 SQL 查询结果类似于：

SELECT relationship.id AS relationship_id, relationship.parent_id AS relationship_parent_id, relationship.child_id AS relationship_child_id, vote.id AS vote_id, vote.rel_id AS vote_rel_id, vote.voter_id AS vote_voter_id 
FROM relationship LEFT OUTER JOIN vote ON relationship.id = vote.rel_id 
WHERE relationship.parent_id = ? AND vote.voter_id = ? AND vote.rel_id = relationship.id

如果您检查它，您会注意到子句 vote. rel_id=relationship.id是JOIN子句和WHERE子句的一部分，这使得查询过滤掉那些Relationship 没有按要求投票的行用户。

解决方案：

1. 从查询中删除多余的filter(Vote.rel_id == Relationship.id).部分。
2. Edit-1：同时将用户 filter(Vote.voter_id == thisuser.id) 的过滤器移出 WHERE 并进入 LEFT JOIN 子句：outerjoin(Vote, and_(Relationship.id == Vote.rel_id, Vote.voter_id == thisuser.id))。