CODEWITHSUNDEEP
mysqlsqlperformance
AlexioVay
AlexioVay

Reputation: 4527

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

Upvotes: 0

Views: 48

Answers (1)

Rick James
Rick James

Reputation: 142298

Change those cases to

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

Upvotes: 1

Related Questions