CODEWITHSUNDEEP
phpmysqlsql
Lekhnath
Lekhnath

Reputation: 4625

get all comments including its votes for a post

I have following three tables in mysql.

postid | post_content => posts

commentid | post | comment_content => comments

commentvoteid | comment | voter => comment_votes

I want to fetch all the comments, counting its votes for a post.

commentid | comment_content | comment_votes => my_result

I have tried the following query but not getting the desired result.

SELECT commentid,comment_content,count_comments.total AS comment_votes
FROM comments
INNER JOIN(SELECT COUNT(*) AS total FROM comment_votes WHERE comment=comments.commentid) AS count_comments
WHERE post={$postId}

Is it possible to fetch the result as I wanted? How can I do that?

Upvotes: 0

Views: 223

Answers (3)

ingwy
ingwy

Reputation: 191

maybe like this:

select a.commentid, a.comment_content, count(b.*) as total from (
select commentid, comment_content from comments where post={$postId}) a
join comment_votes b on b.comment = a.commentid
group by a.commentid, a.comment_content;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The method that you are trying uses a correlated subquery. You can do this, but the correlated subquery needs to go into the select clause:

SELECT c.commentid, c.comment_content,
       (SELECT COUNT(*) FROM comment_votes cv WHERE cv.comment = c.commentid
       ) AS comment_votes
FROM comments c
WHERE post={$postId};

Normally, I much prefer the group by approach but sometimes in MySQL this can be faster.

Upvotes: 2

mesutozer
mesutozer

Reputation: 2859

You can use GROUP BY to achieve what you want:

SELECT commentid,comment_content,COUNT(*) AS total
FROM comments
INNER JOIN comment_votes ON (comment_votes.comment=comments.commentid)
WHERE post={$postId}
GROUP BY commentid;

Upvotes: 3

Related Questions