CodeIgniter Forums
Multiple joins and multiple counts - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Multiple joins and multiple counts (/thread-13336.html)



Multiple joins and multiple counts - El Forum - 11-19-2008

[eluser]Daniel H[/eluser]
I know I should be posting this in an db forum, but I hope this is an easy problem someone can help me with...

Essentially I am trying to return the comment count for a given post. I'm doing this using a LEFT join and aggregate COUNT, however because I need to left join the category/tags table to the post, the query I'm using multiplies the number of tags/categories by the number of comments!

Here's a simplified query... can anyone suggest how I ought to be doing this? Maybe my comment counts have to be separate queries...?


Code:
SELECT DISTINCT posts.id, posts.title,

    COUNT(post_comment.comment_id) AS comment_count,
    COUNT(taxonomy.id) AS taxonomy_count
    
FROM posts

    LEFT JOIN post_comment ON posts.id = post_comment.post_id
    INNER JOIN comments ON post_comment.comment_id = comments.id

    LEFT JOIN post_taxonomy ON posts.id = post_taxonomy.post_id
    INNER JOIN taxonomy ON post_taxonomy.taxonomy_id = taxonomy.id

GROUP BY posts.id
ORDER BY posts.id DESC



Multiple joins and multiple counts - El Forum - 11-19-2008

[eluser]Daniel H[/eluser]
...now I'm using two subqueries to get the counts... not sure if that is good practice?!

Code:
SELECT posts.id, posts.title,

    (SELECT COUNT(c.id) AS c_count FROM posts AS p LEFT JOIN post_comment AS pc ON p.id = pc.post_id INNER JOIN comments AS c ON pc.comment_id = c.id WHERE p.id = posts.id) AS comment_count

FROM posts

ORDER BY posts.id DESC



Multiple joins and multiple counts - El Forum - 11-19-2008

[eluser]Phil Sturgeon[/eluser]
Your sub query approach will work out better. Before you probably had problems as each time you are joining tables together you are saying "all the fields should be returned together". This means that the count will count empty strings all over the place, and give you identical or screwy results.

A sub query for each count is usual best practice.


Multiple joins and multiple counts - El Forum - 11-19-2008

[eluser]Daniel H[/eluser]
Okay great - thanks Phil,

Dan.