CODEWITHSUNDEEP
phpmysql
AlexioVay
AlexioVay

Reputation: 4527

Exclude a specific SQL column?

I'm trying to SELECT random pages that haven't been liked or disliked by a certain user (user-id: uid) yet.

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 liked or disliked, because WHERE l.uid != 1 doesn't affect to exclude a whole l.page_id (because there are other likers and dislikers for a particular page_id).

I guess I need to solve this with a subquery to receive a page id first or use some specific algorithm? I also thought of storing all liked and disliked pages as an array in a cookie or session, but I don't know if that's the right or efficient way to do it?

Upvotes: 5

Views: 215

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

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 the likes table.

EDIT:

This variation on your query should do about 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.

Upvotes: 4

Related Questions