Vaia Vaia - 20 days ago 8
MySQL Question

Show entries that I have in common with an user, seperated by likes and dislikes

I have a page system like the one on facebook where every user can like or even dislike a page.

status = 1
is a like,
0
is a dislike.

This is my query:

SELECT
p.title_de, de, p.keyname, l.status,
(CASE WHEN l.status = '1' THEN title_de END) AS liked,
(CASE WHEN l.status = '0' THEN title_de END) AS disliked
FROM pages_likes l
JOIN pages p on p.id = l.page_id
WHERE p.keyname != 'rship' AND l.uid = '311'
GROUP BY l.page_id
ORDER BY p.title_de ASC


Questions:

1) I also want to count all liked and disliked entries by that user. At the moment I wrote an extra query for that but I wonder if it would be more efficient to implement that count in that query already? I tried to use
COUNT
but then I only get one result back from the whole query.

2) I want to display common/mutual likes I have with this user (
uid
means user_id). I tried to add an
AND 'uid' = 1
to
WHERE
but that didn't work. This should also be seperated by likes and dislikes (
status = 1
and
0
). I suppose I should write a subquery with
UNION
? But I don't know how exactly.

This is my structure:

enter image description here

Answer

Change those cases to

SUM(l.status = 1) AS likes,
SUM(l.status = 0) AS dislikes,
Comments