How to form this sql query in order to check for multiple specific values without causing duplicates?

Question

I have the following table with these columns: the_comment_liked, user_who_liked, value.

I set up an SQL fidle here.

And this is part of the query that I use:

SELECT the_comment_liked,
    COUNT(CASE WHEN value = '1' then '1' ELSE NULL END) AS tot_like,
    COUNT(CASE WHEN value = '2' then '1' ELSE NULL END) AS tot_dis,
    GROUP_CONCAT(user_who_liked) AS user_who_liked
FROM like_dislike_comm
GROUP BY the_comment_liked

Now I perform a PHP check to see if the user has liked or disliked like this:

 if (strpos($r_com['user_who_liked'] ,$_SESSION["currentUser"]) !== false){//set a variable}
 //$_SESSION["currentUser"] (this stores the id of the current logged user)

If this returns true I set a class to a variable in order to highlight the like or dislike button, the problem is that I also have to check for the value in order to know if it's a like or a dislike, something like:

 if (strpos($r_com['user_who_liked'] ,$_SESSION["currentUser"]) !== false && $r_com['value'] === 1){}

Now I can do that by selecting the value as well, but that gives me duplicates. How can I form the query in order to check not only if the user id is there but also the value, a like or a dislike? Or in what other ways I could perform a check like this?

I need to have everything grouped so I can display the total amount of likes and dislikes on a comment and at the same time, I need for each user_who_liked to check if the value is an 1(like) or a 2(dislike) so I can highlight the like or dislike button on each comment in order for the logged user to see what he liked or not. I'm also not sure that using GROUP_CONCAT(user_who_liked) is the right approach.

Mysql version is 5.7.14

Answer 1

You already know how to do conditional aggregations (COUNT(CASE ..)). To find the value of the current user per comment, you just need one more. Assuming the ID of current user is 118, it would be something like:

MAX(CASE WHEN user_who_liked = 118 THEN value ELSE NULL END) AS value_of_current_user

It doesn't matter if you use MAX or MIN here, since there should be only one value. You can even use GROUP_CONCAT.

Note that NULL is default value for the ELSE case, so you can omit it:

MAX(CASE WHEN user_who_liked = 118 THEN value END) AS value_of_current_user

You can also write it a bit shorter:

MAX(CASE user_who_liked WHEN 118 THEN value END) AS value_of_current_user

So your final query could be:

SELECT the_comment_liked,
    COUNT(CASE value WHEN 1 then 1 END) AS tot_like,
    COUNT(CASE value WHEN 2 then 1 END) AS tot_dis,
    GROUP_CONCAT(user_who_liked) AS user_who_liked,
    MAX(CASE user_who_liked WHEN 118 THEN value END) AS value_of_current_user
FROM like_dislike_comm
GROUP BY the_comment_liked

SQL-Fiddle

If you also whant to split the ID list into users_who_liked and users_who_disliked, you can use conditional aggregation for GROUP_CONCAT too:

SELECT the_comment_liked,
    COUNT(CASE value WHEN 1 then 1 END) AS tot_like,
    COUNT(CASE value WHEN 2 then 1 END) AS tot_dis,
    GROUP_CONCAT(CASE value WHEN 1 THEN user_who_liked END) AS user_who_liked,
    GROUP_CONCAT(CASE value WHEN 2 THEN user_who_liked END) AS user_who_disliked,
    MAX(CASE user_who_liked WHEN 118 THEN value END) AS value_of_current_user
FROM like_dislike_comm
GROUP BY the_comment_liked

SQL-Fiddle

Answer 2

Unless I misunderstand something, each time you run this query, it's likely for only one user (the one logged in).

If this is the case, then it's pretty inefficient to have your SQL not help you out more.

The solution below is hard-coded for user 118. Your PHP should inject this parameter where you see 118, OR, you can turn this code into a MySQL stored procedure that takes the user as a parameter.

The output shows all comments, the total likes, total dislikes, and what the vote for that one user is (1 = like, 2 = dislike, null = no vote).

select
  totals.the_comment_liked,
  totals.tot_like,
  totals.tot_dis,
  like_or_dislike.value as this_users_vote
from
  (
    select
      the_comment_liked,
      sum(case when value = 1 then 1 else 0 end) as tot_like,
      sum(case when value = 2 then 1 else 0 end) as tot_dis
    from
      like_dislike_comm
    group by
      the_comment_liked
  ) as totals
  left outer join (
    select 
      the_comment_liked,
      value
    from
      like_dislike_comm
    where
      /* This value has to be dynamically inserted by your PHP, 
         OR you can make this a parameter in a stored procedure */
      user_who_liked = 118 
  ) as like_or_dislike on
    totals.the_comment_liked = like_or_dislike.the_comment_liked

This returns the following result set (again, this is hard-coded for user 118):

the_comment_liked   tot_like    tot_dis this_users_vote
310 0   1   2
311 1   1   1
312 1   2   2
313 1   2   1
314 0   3   2
315 2   1   2
339 1   0   (null)
340 1   0   (null)
341 1   0   (null)
347 1   0   (null)
353 1   0   (null)
360 1   1   2
365 0   1   (null)
366 1   1   (null)
370 1   2   1
378 1   0   (null)
380 1   0   (null)
387 2   0   (null)
388 1   0   (null)
394 1   1   (null)
395 0   1   (null)
426 1   0   (null)
430 2   0   (null)
439 0   1   (null)