How to get all users that the logged in user is not friends with in MySQL?

user_5045446 0
Viewed 594

I am fully able to get all of the logged in users current friends, which I do with this query here - 

SELECT users.*
FROM users
LEFT JOIN friends
ON users.id = friends.friend_id
WHERE friends.user_id = $user_logged_in_id

But I want to do the opposite, and I can't figure it out.

I am trying to do friends logic.

I need to get all the users who the currently logged in user is not already friends with

I have a users table and a friends table to act as a many to many relationship between users and their friends. The keys in the friends table are user_id friend_id.

To illustrate that someone is friends with another user I put (say the logged in user id is 3, and friends user id is 6) user_id = 3 and friend_id = 6 as one row, and then put another row with the id's flipped to illustrate the friendship the other way, user_id = 6 and friend_id = 3.

users table

|---------------------|------------------|
|          id         |     username     |
|---------------------|------------------|
|          1          |     sonja12      |
|---------------------|------------------|
|          2          |     dorris32     |
|---------------------|------------------|
|          3          |     billy32      |
|---------------------|------------------|
|          4          |     micha97      |
|---------------------|------------------|
|          5          |     travis841    |
|---------------------|------------------|
|          6          |     johnny28     |
|---------------------|------------------|

friends table

|---------------------|------------------|------------------|
|          id         |     user_id      |     friend_id    |
|---------------------|------------------|------------------|
|          1          |        3         |         6        |
|---------------------|------------------|------------------|
|          2          |        6         |         3        |
|---------------------|------------------|------------------|

EDIT:

I think I have narrowed down my question a bit to be more concise.

I want to get all users from the users table where their id does not appear as a friend id beside the user_id of the current logged in user in the friends table.

many-to-many join mysql sql php
4 Answers

You're thinking of the problem in a strange way. It might help to think of it as an intersection of sets you need to create as opposed to a set that exists in the absence of an id.

SELECT id from users WHERE id != $logged_in_user AND id NOT IN ( SELECT friend_id from friends where user_id=$logged_in_user)

user_61686870

For bigger Datasets I would use a NOT EXISTS subquery query:

select u.*
from users u
where u.id <> $user_logged_in_id
  and not exists (
    select *
    from friends f
    where f.user_id = $user_logged_in_id
      and f.friend_id = u.id
)

or a LEFT (ANTI) JOIN:

select u.*
from users u
left join friends f
  on  f.user_id = $user_logged_in_id
  and f.friend_id = u.id
where u.id <> $user_logged_in_id
  and f.friend_id is null

I think the execution plan for both queries should be almost the same.

Demos: http://rextester.com/KUOZ9198

user_55630830

SQL

SELECT u.*
FROM usrs u
LEFT JOIN friends f
ON u.id = f.friend_id
GROUP BY u.id, u.username, IF(f.user_id = $user_logged_in_id, 1, 0)
HAVING MAX(IF(f.user_id = $user_logged_in_id, 1, 0)) = 0;

Demo

http://rextester.com/ELXFZ39682

Explanation

The query is based on your original but is grouping by each user ID so duplicates aren't returned. In order to return those that aren't friends of the logged in user, the IF(f.user_id = $user_logged_in_id, 1, 0) adds a generated column to the group that is set to 1 if the row is a friend of the logged in user, otherwise 0. We can then use the HAVING MAX(...) = 0 to restrict to groups where none of the rows is a friend.

user_10637160 
SELECT DISTINCT users.id, users.username
  FROM users
       LEFT OUTER JOIN friends
    ON (friends.user_id = $user_logged_in_id and friends.friend_id = users.id)
 WHERE users.id <> $user_logged_in_id
   AND friends.friend_id is null;

This query is likely to be more performant than a NOT IN Sub Query if the user is expected to have lots of friends. If not NOT IN sub query performs the same if not better.

Please note the DISTINCT. You need this because the relationship is many to many.

user_89086000
Related