Exclude a specific SQL column?

Question

I am trying to SELECT random pages that a user has not liked or disliked (user-id: uid ).

My table structure:

OneNight_pages : (id, title) OneNight_pages_likes : (id, page_id, uid, status)

And this is how I tried to get a random page:

 SELECT p.id AS page_id, p.title, SUM(CASE WHEN l.page_id = p.id AND status = '1' THEN 1 ELSE 0 END) AS likes, SUM(CASE WHEN l.page_id = p.id AND status = '0' THEN 1 ELSE 0 END) AS dislikes FROM OneNight_pages_likes l LEFT JOIN OneNight_pages p on l.page_id = p.id WHERE l.uid != '1' GROUP BY page_id ORDER BY rand() LIMIT 1 

However, this will still display pages that I already like or dislike, because WHERE l.uid != 1 does not affect the exclusion of the whole l.page_id (because for a specific page_id) there are other sheets and nonhumans.

I think I need to solve this using a subquery in order to get the page id first or use some specific algorithm? I also thought about saving all my favorite and least favorite pages as an array in a cookie or session, but I don't know if this is the right or efficient way to do this?

Answer 1

Is this what you want?

 select p.* from OneNight_pages p where not exists (select 1 from OneNight_pages_likes l where l.page_id = p.id and l.uid = '1' ) order by rand() limit 1; 

This selects a random page where user "1" has no rows in a table like that.

EDIT:

This variant of your query should do roughly the same thing:

 SELECT p.id AS page_id, p.title, SUM(CASE WHEN status = '1' THEN 1 ELSE 0 END) AS likes, SUM(CASE WHEN status = '0' THEN 1 ELSE 0 END) AS dislikes FROM OneNight_pages p LEFT JOIN OneNight_pages_likes l ON l.page_id = p.id GROUP BY page_id HAVING SUM(l.uid = 1) = 0 ORDER BY rand() LIMIT 1; 

The previous version should have better performance.