CODEWITHSUNDEEP
mysql
Jeremy Kalgreen
Jeremy Kalgreen

Reputation: 71

How do exclude items from a mySQL select, if any one of a subset of users gives a certain item a specifc rating?

I'm working on a little web app where you can rate restaurants or rate how much you want to try them. I have the database set up something like this:

users (name, userid)


ratings(userid,restaurantid,rating(#0-5),visited(boolean))

(if you haven't been to a restaurant before but give it a 0-5 rating the system treats it as how badly you'd like it)

I want to have the ability to compare your ratings / wish list with other users but I've run into a bit of a problem. I know how to join the tables and get the sum of the ratings for each user in the comparison, so that the restaurants with the highest ratings float to the top by using this:

SELECT ratings.rest_id, SUM( ratings.rating ) AS Sum
FROM names
LEFT JOIN ratings ON ratings.rest_id = names.id
WHERE ratings.user_id IN ( userid1, userid2, userid3 ) 
GROUP BY ratings.rest_id
ORDER BY  `Sum` DESC , names.name ASC

But I want users to be able to modify the search so it only includes restaurants that all of the included users have set to either 'visited' (so you can see the the overall favorite restaurants of the included users that all of them have actually been to), or 'unvisited' (so you can come up with a list of the places that no one has been to yet, but everyone wants to try the most).

If I just add a "AND ratings.visited != 1" clause to the end of the WHERE it will exclude the ratings of the particular user who had the visited column set to 1 (visited), but it will still include the other user's ratings for that restaurant (which I don't want). For example if you have the following setup:

User | Restaurant | rating | visited
------------------------------------
pam  |   subway   |   5    |    1
joe  |   subway   |   3    |    0
bob  |   subway   |   1    |    0

pam  |   bigboy   |   4    |    0
joe  |   bigboy   |   3    |    0
bob  |   bigboy   |   4    |    0

pam  |  tacobell  |   2    |    1
joe  |  tacobell  |   2    |    1
bob  |  tacobell  |   5    |    1

Currently by adding the "AND ratings.visited != 1" clause and running the above search the result would be: 

rest_id | Sum
-------------
subway  |  4
bigboy  | 11

Where subway is still included, despite Pam having visited it (only her rating was excluded). When what I want is for the search to only return the sum for bigboy, as it was the only restaurant that all of the included visitors have never been to.

Anyone able to point me in the right direction?

Thanks! Jeremy

Upvotes: 1

Views: 54

Answers (1)

sagi
sagi

Reputation: 40481

You can solve this by using NOT EXISTS() :

SELECT ratings.rest_id, SUM( ratings.rating ) AS Sum
FROM names
LEFT JOIN ratings ON ratings.rest_id = names.id
 AND ratings.user_id IN ( userid1, userid2, userid3 ) 
WHERE NOT EXISTS(SELECT 1 FROM ratings r 
                 WHERE r.visited = 1 AND r.rest_id = ratings.rest_id)
GROUP BY ratings.rest_id
ORDER BY  `Sum` DESC , names.name ASC

Also, as you can see I've moved the ratings.user_id IN ( userid1, userid2, userid3 ) condition to the ON clause. When left joining, the conditions on the right table should be in the ON , when being specified in the WHERE clause, the left join automatically turns into an inner join.

Upvotes: 1

Related Questions