How to join these two querys in one?

Question

I have a table that records user reviews in Forum responses in rating, where: 0 = Vote No; 1 = Positive vote; 3 = Vote Annulled

If I want to take all votes in response from a single user on a topic I do the following:

SELECT reply_id, rating 
FROM `forum_ratings` 
WHERE topic_id = '4' and user_id = '29'

I have another query that takes all votes and groups by answer id, which in this case is the reply_id field.

SELECT reply_id, 
sum(rating = '1') as likes, 
SUM(rating = '0') as unlikes 
FROM `forum_ratings` 
WHERE topic_id = '4' 
GROUP BY reply_id

I would like to group and at the same time have user X vote in front of the result for each reply_id. If the user has not voted either positive or negative in a response, the number 3 is shown. Something like:

reply_id    likes   unlikes current_user
6            234       12        0
7            122       44        1
8            14         5        3 

Answer 1

Assuming a user has only one vote per question, you can do it as follows, for example:

SELECT A.reply_id, 
       SUM(case when A.rating = '1' then 1 else 0 end) as likes,
       SUM(case when A.rating = '0' then 1 else 0 end) as unlikes,
       MAX(COALESCE(CurrentUser.rating, 3)) as `current_user`
  FROM forum_ratings A
  LEFT JOIN
  (
    SELECT reply_id, rating FROM forum_ratings WHERE user_id = 29 
  ) AS CurrentUser 
    ON CurrentUser.reply_id = A.reply_id
 WHERE A.topic_id = '4' 
 GROUP BY A.reply_id

The MAX function is used because of the aggregation being done at the response level (reply_id). In this case, if the user with id = 29 has voted in the answer we show which was the vote (1, 2, or 3). Otherwise, we show 3.

I think that this alternative should also produce the expected result (it gave the right result in few tests I’ve done)

SELECT A.reply_id, 
       SUM(CASE WHEN A.rating = '1' THEN 1 ELSE 0 END) as likes,
       SUM(CASE WHEN A.rating = '0' THEN 1 ELSE 0 END) as unlikes,
       MIN(CASE WHEN A.user_id = 29 THEN a.rating ELSE 3 END) as `current_user`
  FROM forum_ratings A
 WHERE A.topic_id = '4' 
 GROUP BY A.reply_id