Suggestions of users to follow-CodePudding

I'm working on social media site, but I can't get a logic that how do I give a list of suggested users to follow.

Users table :-

id  |  name  |  username  |  password

Relationship table :-

id  |  followee  |  follower

where followee and follower are the foreign keys to users table. I tried some sql joins and also put some logic in backend but that didn't worked. Here is what I tried :-

select u.id, u.name from users u, relationship r on u.id = r.follower where u.id != (user_id) and r.follower != (user_id)

Here in user_id I have to pass the id of the logged in user, so that I want to return the list of users whom current user don't follow and if relationship table is empty then it must return all the users from users table except current user.

Here I also want to return distinct values from users table and follower column.

Here image I have provided an image of sample data. If current user id 187 follows all users then it must return empty data.

if current user id 188 doesn't follow anybody then it must return data of 187 and 198.

CodePudding user response：

You want to use NOT IN:

SELECT u.id, u.name FROM users u
WHERE u.id NOT IN (
  SELECT r.followee FROM relationship
  WHERE r.follower = (user_id) 
) AND u.id != (user_id)