The tables in this query are as follows:<ul><li>Post</li> <li>User</li> <li>Comment</li> <li>Tag</li> <li>Tagged_Post</li> <li>Post_Category</li> </ul>
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.
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";
This outputs the following:
Array (  => 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 )  => 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 )  => 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 ) )
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?Answer1:
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.
To fix, use:
... COUNT(DISTINCT comment.comment_id) as comments