Vaia Vaia - 2 months ago 16
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,
is a dislike.

This is my query:

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 = l.page_id
WHERE p.keyname != 'rship' AND l.uid = '311'
GROUP BY l.page_id
ORDER BY p.title_de ASC


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
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 (
means user_id). I tried to add an
AND 'uid' = 1
but that didn't work. This should also be seperated by likes and dislikes (
status = 1
). I suppose I should write a subquery with
? But I don't know how exactly.

This is my structure:

enter image description here


Change those cases to

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