Friends of Friends of Friends Request
This question is related to the previous one. You can check my first post here
I am trying to fetch data from a users table and I want "friends of friends", those who are two steps away from the selected user but not directly related to the selected user
I tried with this query:
select
u.*
from user u
inner join friend f
on u.user_id = f.friend_id
inner join friend ff
on f.user_id = ff.friend_id
where ff.user_id = {$user_id} AND u.user_id <> {$user_id};
I didn't know how to attract users that are not directly connected to the selected user. I get all friends of friends of the current user, but I also get direct friends from the current user.
source to share
You just need to exclude those who are direct friends and also be friends of friends. I changed the table aliases to make it a little clearer (to me, anyway) what I got:
SELECT
u.*
FROM
user u
INNER JOIN friend ff ON u.user_id = ff.friend_id
INNER JOIN friend f ON ff.user_id = f.friend_id
WHERE
f.user_id = {$user_id}
AND ff.friend_id NOT IN
(SELECT friend_id FROM friend WHERE user_id = {$user_id})
It also removes the need to exclude the requested user ID.
source to share
You are on the right track. You need to add a condition that excludes those directly related:
select u.*
from user u
inner join friend f on u.user_id = f.friend_id
inner join friend ff on f.user_id = ff.friend_id
where ff.user_id = {$user_id} AND u.user_id <> {$user_id};
AND not exists
(select f2.friend_id
from friend f2
where f2.friend_id = ff.friend_id
and u.user_id = f2.user_id)
The additional sentence not exists
I added to your request confirms that a second degree friend is not a first degree friend either.
source to share
I would use an uncorrelated subquery that extracts direct friendship IDs {$user_id}
and excludes those users from the final result:
select u.*
from user u
inner join friend f on u.user_id = f.friend_id
inner join friend ff on f.user_id = ff.friend_id
where ff.user_id = {$user_id} AND u.user_id <> {$user_id}
AND u.user_id not in (
select directFriend.friend_id
from friend directFriend
where directFriend.user_id = {$user_id})
source to share