将计数( "reviews" .id) + 计数( "comments" .id) 汇总为my_count时出现奇怪的结果

Question

在下面的查询中，我计算了出版物有多少评论和评论。我总结这样的评论和评论：.select（'"publications".*， count（"reviews".id） + count（"comments".id） 作为my_count'）。

假设出版物有 3 条评论和 3 条评论，总共是 6 条，但是my_count总是显示更大的数字。幕后发生了什么，如何让它正常计数？

Publication.joins(:reviews, :comments)
                                  .select('"publications".*, count("reviews".id) + count("comments".id) as my_count')
                                  .group('"publications".id')
                                  .order("my_count DESC")

Answer 1

生成的SQL可能如下所示：

SELECT publications.id, COUNT(reviews.id) + COUNT(comments.id) AS my_count
FROM publications p
INNER JOIN reviews r ON p.id = r.publication_id
INNER JOIN comments c ON p.id = c.publication_id
GROUP BY p.id
ORDER BY my_count DESC

让我们暂时摆脱分组，看看以下输入发生了什么：

publications: [{ id: 1 }],
reviews: [{ publication_id: 1, id: 1 }, { publication_id: 1, id: 2 },{ publication_id: 1, id: 3 }]
comments: [{ publication_id: 1, id: 10 }, { publication_id: 1, id: 20 }]

所以有 3 条评论和 2 条评论。但是，此查询将返回 6 行：

SELECT *
FROM publications p
INNER JOIN reviews r ON p.id = review.publication_id
INNER JOIN comments c ON p.id = comment.publication_id
publication.id | review.id | comment.id
1              | 1         | 10
1              | 2         | 10
1              | 3         | 10
1              | 1         | 20
1              | 2         | 20
1              | 3         | 20

而且，当您对其进行分组时，它将返回 6+6 = 12 作为总数。一种可能的解决方法是执行COUNT(DISTINCT reviews.id) + COUNT(DISTINCT comments.id)。就性能而言，这可能不是最佳解决方案。

Answer 2

正如 DNNX 指出的那样，发生这种情况是因为您陷入了所谓的"鸿沟陷阱"——由于多个一对多连接，行计数膨胀了。

您可以尝试将其作为短期替代方案：

Publication.select('"publications".*,
                     coalesce((select count(*) from reviews r where r.publication_id = pubications.id),0)
                     + coalesce((select count(*) from comments c where c.publication_id = pubications.id),0) as my_count')
            .order("my_count DESC")

但是，我建议您在发布上放置计数器缓存以进行评论和评论。