Mysql查询搜索帖子和评论，返回总评论数

Question

我正在尝试编写一个查询

在表格帖子和评论中搜索字符串

如果任一表中存在匹配项，则返回匹配的帖子及其总评论数。

我有:

SELECT Posts.id, Comments.id, Count(DISTINCT Comments.id)
FROM Posts 
LEFT JOIN Comments ON Comments.postid = Posts.id 
WHERE ( Posts.text LIKE '%test%' 
          OR ( Comments.text LIKE '%test%'  
          AND Comments.deleted = false ) ) 
       AND Posts.approved = true 
       AND Posts.deleted = false 
GROUP BY Posts.id

此查询的问题在于 where 正在过滤我的 Comments.id 计数，因此它不会返回帖子中的评论总数，而是返回与“test”匹配的评论数。
如何在单个查询中实现我想要的？

Answer 1

其实GROUP BY不需要条款:

SELECT Posts.id, 
    ( SELECT COUNT(*) FROM Comments c 
      WHERE c.postid = Posts.id AND c.deleted = false
    ) AS total_post_comments
FROM Posts 
WHERE Posts.approved = true 
    AND Posts.deleted = false 
    AND (
        Posts.text LIKE '%test%'
        OR EXISTS(
            SELECT 1 FROM Comments
            WHERE Comments.text LIKE '%test%'
              AND Comments.deleted = false
              AND Comments.postid = Posts.id
        )
    )