SQL - 受其他连接影响的相关表的查询计数 [英] SQL - Query count for related table affected by other join

问题描述

此查询中的表如下:

• 发布
• 用户
• 评论
• 标签
• Tagged_Post
• Post_Category

我正在尝试查询有关帖子的所有相关信息，这些信息具有诸如发布帖子的用户、对该特定帖子的评论、帖子上的多标签或无标签以及帖子所属的类别等关系.

I'm trying to query all relevant information about a post which has relations such as the User who made the post, comments on that specific Post, the many or none tags on the Post, and category that the Post is in.

这是我的 SQL 查询:

Here is my SQL query:

$sql = "SELECT post.*, user.name, user.avatar, group_concat(DISTINCT tag.slug) as tags, post_category.slug as category, count(comment.post_id) as comments
FROM post
INNER JOIN user on user.id = post.user_id
INNER JOIN post_category on post_category.id = post.category_id
LEFT JOIN tagged_post on tagged_post.post_id = post.id
LEFT JOIN tag on tagged_post.tag_id = tag.id
LEFT OUTER JOIN comment on post.id = comment.post_id
GROUP BY post.id";

输出如下:

Array
(
    [0] => Array
        (
            [id] => 1
            [user_id] => 1
            [category_id] => 1
            [title] => Hi, I'm Bob Ross. AMA
            [body] => That's right. I'm bob ross and this is my post. I'm not dead btw
            [date_created] => 2018-09-02 11:45:29
            [date_modified] => 
            [name] => bob_ross
            [avatar] => 
            [tags] => painting,ama
            [category] => news-and-politics
            [comments] => 6
        )

    [1] => Array
        (
            [id] => 2
            [user_id] => 2
            [category_id] => 2
            [title] => I'm Saul Goodman!!
            [body] => woohoo
            [date_created] => 2018-09-02 12:12:12
            [date_modified] => 
            [name] => saul_goodman
            [avatar] => 
            [tags] => 
            [category] => general-discussion
            [comments] => 0
        )

    [2] => Array
        (
            [id] => 3
            [user_id] => 3
            [category_id] => 4
            [title] => yo im jesse
            [body] => test
            [date_created] => 2018-09-02 12:24:45
            [date_modified] => 
            [name] => jesse_pinkman
            [avatar] => 
            [tags] => ama,painting
            [category] => animals-and-nature
            [comments] => 4
        )

)

标签数量似乎影响了评论数量.例如，在第一篇文章中，有 3 个评论和 2 个标签.ID 为 1 的帖子的评论数显示为 6.如果我要在此帖子上添加额外的标签(总共 3 个标签)，则评论数将显示 9(3 个标签 x 3 条评论).

The amount of tags seems to be affecting the count on the comments. For instance, on the first post, there are 3 comments and 2 tags. The count for comments on post with ID of 1, is showing 6. If I were to add an additional tag on this post (3 total tags), then the comment count would display 9 (3 tags x 3 comments).

有人能帮我理解为什么会这样吗?

Can someone help me understand why this is happening?

推荐答案

原因是使用多个 JOIN 就像笛卡尔积一样，所以你得到了 2*3=6 行的组.当您应用计数时，您会得到 6 个有效(非空)值，这就是您的结果.

The reason is that using multiple JOINs acts like a Cartesian product, so you get 2*3=6 rows for the group. When you apply count, you get 6 valid (non-null) values and that's your result.

要修复，请使用:

... COUNT(DISTINCT comment.comment_id) as comments

这篇关于SQL - 受其他连接影响的相关表的查询计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！