添加LEFT OUTER JOIN后投票数统计异常的原因与解决方法

开发Reddit克隆项目时，查询所有帖子需要返回post_id、点赞数、踩数、评论数，但关联Comment表后，点赞/踩数变成了原投票数和评论数的乘积。仅关联Vote表时统计正常，添加Comment的LEFT OUTER JOIN后出现异常。

预期结果 #

实际结果 #

SQLAlchemy查询及生成的SQL #

SQLAlchemy代码 #

posts_query = db.query(
      models.Post.id,
      func.count(models.Vote.post_id).filter(models.Vote.upvote == True).label("num_upvotes"),
      func.count(models.Vote.post_id).filter(models.Vote.upvote == False).label("num_downvotes"),
      func.count(models.Comment.post_id).label('num_comments')
    ).join(
      models.Vote, models.Vote.post_id == models.Post.id, isouter=True
    ).join(
      models.Comment, models.Comment.post_id == models.Post.id, isouter=True
    ).group_by(
      models.Post.id
    ).all()

生成的SQL #

SELECT posts.id AS posts_id,
count(votes.post_id) FILTER (WHERE votes.upvote = true) AS num_upvotes,
count(votes.post_id) FILTER (WHERE votes.upvote = false) AS num_downvotes,
count(comments.post_id) AS num_comments 
FROM posts
LEFT OUTER JOIN votes ON votes.post_id = posts.id
LEFT OUTER JOIN comments ON comments.post_id = posts.id
GROUP BY posts.id

SQLAlchemy模型定义 #

class Post(Base):
  __tablename__ = "posts"

  id = Column(Integer, primary_key=True, nullable=False)
  title = Column(String, nullable=False)
  content = Column(String, nullable=False)
  published = Column(Boolean, server_default="True", nullable=False)
  created_at = Column(TIMESTAMP(timezone=True), 
    server_default=text('now()'), nullable=False)
  owner_id = Column(Integer, ForeignKey("users.id", 
    ondelete="CASCADE"), nullable=False)
  owner = relationship("User", backref="posts")


class User(Base): 
  __tablename__ = "users"

  id = Column(Integer, primary_key=True, nullable=False)
  username = Column(String, nullable=False, unique=True)
  email = Column(String, nullable=False, unique=True)
  password = Column(String, nullable=False)
  created_at = Column(TIMESTAMP(timezone=True), 
    server_default=text('now()'), nullable=False)


class Vote(Base):
  __tablename__ = "votes"

  user_id = Column(Integer, ForeignKey("users.id", 
    ondelete="CASCADE"), primary_key=True)
  user = relationship("User", backref="votes")
  post_id = Column(Integer, ForeignKey("posts.id", 
    ondelete="CASCADE"), primary_key=True)
  post = relationship("Post", backref="votes")
  upvote = Column(Boolean, nullable=False)

class Comment(Base):
  __tablename__ = "comments"

  id = Column(Integer, primary_key=True, nullable=False)
  owner_id = Column(Integer, ForeignKey("users.id", 
    ondelete="CASCADE"))
  owner = relationship("User", backref="comments")
  post_id = Column(Integer, ForeignKey("posts.id", 
    ondelete="CASCADE"))
  post = relationship("Post", backref="comments")
  content = Column(String, nullable=False)
  created_at = Column(TIMESTAMP(timezone=True), 
    server_default=text('now()'), nullable=False)

测试验证 #

测试1：仅关联Vote表，统计正常 #

SQLAlchemy代码

posts_query = db.query(
      models.Post.id,
      func.count(models.Vote.post_id).filter(models.Vote.upvote == True).label("num_upvotes"),
      func.count(models.Vote.post_id).filter(models.Vote.upvote == False).label("num_downvotes"),
    ).join(
      models.Vote, models.Vote.post_id == models.Post.id, isouter=True
    ).group_by(
      models.Post.id
    ).all()

生成的SQL

SELECT posts.id AS posts_id,
count(votes.post_id) FILTER (WHERE votes.upvote = true) AS num_upvotes,
count(votes.post_id) FILTER (WHERE votes.upvote = false) AS num_downvotes, 
FROM posts
LEFT OUTER JOIN votes ON votes.post_id = posts.id
GROUP BY posts.id

查询结果

测试2：关联Vote和Comment表，统计异常 #

SQLAlchemy代码

posts_query = db.query(
      models.Post.id,
      func.count(models.Vote.post_id).filter(models.Vote.upvote == True).label("num_upvotes"),
      func.count(models.Vote.post_id).filter(models.Vote.upvote == False).label("num_downvotes")
    ).join(
      models.Vote, models.Vote.post_id == models.Post.id, isouter=True
    ).join(
      models.Comment, models.Comment.post_id == models.Post.id, isouter=True
    ).group_by(
      models.Post.id
    ).all()

生成的SQL

SELECT posts.id AS posts_id,
count(votes.post_id) FILTER (WHERE votes.upvote = true) AS num_upvotes,
count(votes.post_id) FILTER (WHERE votes.upvote = false) AS num_downvotes 
FROM posts
LEFT OUTER JOIN votes ON votes.post_id = posts.id
LEFT OUTER JOIN comments ON comments.post_id = posts.id
GROUP BY posts.id

查询结果

问题原因分析 #

当同时LEFT JOIN Vote和Comment表时，会产生笛卡尔积：每一条投票记录会和该帖子的所有评论记录进行关联。比如post_id=1有2条点赞记录和5条评论记录，关联后会生成2*5=10条记录，count统计时会把这10条都算进去，导致点赞数变成10，而非原本的2。

修正方案 #

方式1：使用子查询统计投票数和评论数 #

通过子查询分别统计每个帖子的投票数和评论数，再关联主查询，避免笛卡尔积影响：

# 子查询统计投票数
vote_subquery = db.query(
    models.Vote.post_id,
    func.count(models.Vote.post_id).filter(models.Vote.upvote == True).label("num_upvotes"),
    func.count(models.Vote.post_id).filter(models.Vote.upvote == False).label("num_downvotes")
).group_by(models.Vote.post_id).subquery()

# 子查询统计评论数
comment_subquery = db.query(
    models.Comment.post_id,
    func.count(models.Comment.post_id).label("num_comments")
).group_by(models.Comment.post_id).subquery()

# 主查询关联子查询
posts_query = db.query(
    models.Post.id,
    func.coalesce(vote_subquery.c.num_upvotes, 0).label("num_upvotes"),
    func.coalesce(vote_subquery.c.num_downvotes, 0).label("num_downvotes"),
    func.coalesce(comment_subquery.c.num_comments, 0).label("num_comments")
).outerjoin(vote_subquery, vote_subquery.c.post_id == models.Post.id).outerjoin(comment_subquery, comment_subquery.c.post_id == models.Post.id).all()

方式2：使用func.distinct去重统计 #

在count时对Vote的唯一标识（user_id，因为每个用户对同一帖子只能投一次票）进行去重，避免重复统计：

posts_query = db.query(
    models.Post.id,
    func.count(func.distinct(models.Vote.user_id)).filter(models.Vote.upvote == True).label("num_upvotes"),
    func.count(func.distinct(models.Vote.user_id)).filter(models.Vote.upvote == False).label("num_downvotes"),
    func.count(models.Comment.post_id).label('num_comments')
).join(
    models.Vote, models.Vote.post_id == models.Post.id, isouter=True
).join(
    models.Comment, models.Comment.post_id == models.Post.id, isouter=True
).group_by(
    models.Post.id
).all()

内容的提问来源于stack exchange，提问作者snkennedy21