Vaia Vaia - 4 months ago 8
SQL Question

Only select a specific page that has been liked or disliked?

I have 3 tables to store peoples interests with likes and dislikes:

pages
,
pages_likes
,
pages_dislikes


Now I wanted to have a main page to show a random page, but only those that haven't been liked or disliked by the user.

This is my SQL statement:

SELECT p.id AS page_id, d.uid AS duid, l.uid AS luid, p.title FROM pages p
JOIN pages_dislikes d on d.page_id = p.id JOIN pages_likes l on l.page_id = p.id
WHERE d.uid != '3' AND l.uid != '3' ORDER BY rand() LIMIT 1


But this still shows a page that has been liked or disliked, because there are other users liked or disliked the page like this:

enter image description here

How could I only show pages a specific user id hasn't liked or disliked yet?

Answer

Your query should be:

SELECT 
    p.id AS page_id
    ,d.uid AS duid
    ,l.uid AS luid
    ,p.title FROM pages p 
LEFT JOIN pages_dislikes d on d.page_id = p.id 
LEFT JOIN pages_likes l on l.page_id = p.id 
WHERE (d.page_id IS null OR l.page_id is null) AND d.uid != '3' AND l.uid != '3'
ORDER BY rand() LIMIT 1

You can also try this query:

SELECT P.pageid, P.pagename FROM Pages P LEFT JOIN
(
    SELECT pageid FROM lik WHERE uid <> 3
    UNION 
    SELECT pageid FROM dis WHERE uid <> 3
) LikesDislikes ON LikesDislikes.pageid = P.pageid
ORDER BY rand() LIMIT 1