CODEWITHSUNDEEP
phpmysql
Holapress
Holapress

Reputation: 111

php query with 2 counts and joins

im running a query that usses 3 tabels "post, likes and comment" i need to get the ammount of likes and comments the post has got, and at the same time get the basic info from the post table so im using the query bellow but the problem is that it copys the value likeAmount to commentAmount if likes is bigger unless comments is 0.

SELECT post.*, COUNT(likes.id) as 'LikeAmount', COUNT(comment.id) as 'commentAmount' FROM post 
  LEFT JOIN likes ON post.id = likes.post
  LEFT JOIN comment ON post.id = comment.post
  GROUP BY post.id 
  ORDER BY LikeAmount DESC"

so that doesnt work but when i add distinct it does work, so when its like this:.

SELECT post.*, COUNT(distinct likes.id) as 'LikeAmount', COUNT(distinct comment.id) as 'commentAmount' FROM post 
LEFT JOIN likes ON post.id = likes.post
LEFT JOIN comment ON post.id = comment.post
GROUP BY post.id 
ORDER BY LikeAmount DESC";

i dont see why it works with distinct and doesnt with out, and does distinct mather performance wise or does it make no diffrence sinds it will be used in a website that has a lott of trafic..

Upvotes: 2

Views: 85

Answers (2)

BD.
BD.

Reputation: 890

Perhaps using SUM instead of COUNT to handle the records where there is no join would work, and should perform just as fast:

SELECT post.id, 
       SUM(IF(likes.id IS NULL,0,1)) as 'LikeAmount', 
       SUM(IF(comment.id IS NULL,0,1)) as 'commentAmount' 
FROM   post 
  LEFT JOIN likes ON post.id = likes.post
  LEFT JOIN comment ON post.id = comment.post
GROUP BY post.id 
ORDER BY LikeAmount DESC"

Upvotes: 0

silly
silly

Reputation: 7887

try this, not short, but readable:

SELECT
    p.*,
    pl.like_count,
    pc.comment_count
FROM post p

#join likes
LEFT OUTER JOIN (
    SELECT
        post,
        COUNT(*) AS like_count
    FROM likes
    GROUP BY post
) AS pl
    ON pl.post = p.id

#join comments
LEFT OUTER JOIN (
    SELECT
        post,
        COUNT(*) AS comment_count
    FROM comment
    GROUP BY post
) AS pc
    ON pc.post = p.id

Upvotes: 2

Related Questions