SQL帮助: COUNT aggregate,条目列表及其评论计数

Question

因此，我想要做的是获取条目/帖子列表及其类别和用户详细信息，以及每个已发表的评论总数。 (条目、类别、用户和评论是单独的表格)

下面的查询可以很好地获取记录，但它似乎跳过了那些没有注释的条目。据我所知，连接很好(评论表上的 LEFT JOIN)，并且查询是正确的。我错过了什么？

SELECT entries.entry_id, entries.title, entries.content,
entries.preview_image, entries.preview_thumbnail, entries.slug,
entries.view_count, entries.posted_on, entry_categories.title AS category_title,
entry_categories.slug AS category_slug, entry_categories.parent AS category_parent,
entry_categories.can_comment AS can_comment, entry_categories.can_rate AS can_rate,
users.user_id, users.group_id, users.username, users.first_name, users.last_name,
users.avatar_small, users.avatar_big, users.score AS user_score, 
COUNT(entry_comments.comment_id) AS comment_count

FROM (entries)
JOIN entry_categories ON entries.category = entry_categories.category_id
JOIN users ON entries.user_id = users.user_id
LEFT JOIN entry_comments ON entries.entry_id = entry_comments.entry_id

WHERE `entries`.`publish` = 'Y'
AND `entry_comments`.`publish` = 'Y'
AND `entry_comments`.`deleted_at` IS NULL
AND `category` = 5

GROUP BY entries.entry_id, entries.title, entries.content,
entries.preview_image, entries.preview_thumbnail, entries.slug,
entries.view_count, entries.posted_on, category_title, category_slug,
category_parent, can_comment, can_rate, users.user_id, users.group_id,
users.username, users.first_name, users.last_name, users.avatar_big,
users.avatar_small, user_score

ORDER BY posted_on desc

编辑:我正在使用 MySQL 5.0

Answer 1

好吧，您正在对entry_comments 进行左连接，条件是:

`entry_comments`.`publish` = 'Y'
`entry_comments`.`deleted_at` IS NULL

对于没有评论的条目，这些条件是错误的。我想这应该可以解决问题:

WHERE `entries`.`publish` = 'Y'
AND (
        (`entry_comments`.`publish` = 'Y'
        AND `entry_comments`.`deleted_at` IS NULL)
    OR
        `entry_comments`.`id` IS NULL
    )
AND `category` = 5

在OR条件中，我输入了entry_comments.id，假设这是entry_comments表的主键，所以你应该将其替换为真实的主键条目_评论。