Reputation: 1784
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.
Upvotes: 3
Views: 776
Reputation: 1484
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.
Upvotes: 0
Reputation: 31832
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
Upvotes: 1
Reputation: 39464
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.
Upvotes: 0
Reputation: 1842
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)
Upvotes: 2