52695

SQL - Query count for related table affected by other join

Question:

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 ( [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 ) )

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

Recommend

  • SQL - Query count for related table affected by other join
  • inner join wont work with mysqli prepared statement in php
  • Laravel Method sync does not exist
  • How to make this query in Oracle
  • CakePHP HABTM question
  • Take values from ArrayList [closed]
  • Query Execution [closed]
  • Implement activity in fragment android
  • Hibernate HQL Subquery
  • Doctrine/Symfony2 Relationship Between 2 Entities over 2 Forms
  • A database schema for Tags (eg. each Post has some optional tags)
  • Complex sql query and JPQL
  • JSON to listView in android
  • PHP: Active record table joins
  • Using SUM() in the SELECT is better or in the SUB-SELECT?
  • multidimensional array conversion is not working
  • Select post from two tag (AND)
  • Searchbox for Custom Listview
  • parsing Json File with gson: MalformedJsonException
  • Raw SQL to SQLAlchemy
  • MySQL query to search for items with certain tags
  • Why I can't use the name of the derived table(subquery result) again?
  • DateTime limits of scale_color_gradient not working only when in for loop in R
  • Weird race conditions when I send high-frequency requests to my datacontext using EF (and WebAPI)
  • Creating a filterable list using Laravel and Eloquent queries [closed]
  • How do I browse databases on an Oracle Server?
  • Overwriting/Adding an ActiveRecord association dynamically using a singleton class
  • MySQL Invalid query: Too high level of nesting for select
  • Get Distinct rows from a result of JOIN in SQL Server
  • How do I conditionally select a field from one of two tables?
  • SQL duplicate rows with multiple left joins
  • Basic many-to-many left join query
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '