CODEWITHSUNDEEP
mysqlsql
eMRe
eMRe

Reputation: 3247

How to get friends of friends of friends...

I am trying to get friends of friends of friends. I get results back but I am not sure if my query is correct. I am using nested select.

My question is:

  1. Is the query correct?

  2. How can i do this query using join?

This is my friend table

rowId  userId  friendId
-----  ------  --------
1      1       4
2      1       2
3      2       10
4      3       6
..     ..      ..

This is what i do to get friends:

SELECT DISTINCT(friendId) FROM `user_friend`
WHERE userId = $userID

This is what I do to get friends of friends

SELECT DISTINCT(friendId) FROM `user_friend`
WHERE userId IN (SELECT DISTINCT(friendId) FROM `user_friend`
                WHERE userId = $userID)

This is what I do to get friends of friends of friends

SELECT DISTINCT(friendId) FROM `user_friend`
WHERE userId IN (SELECT DISTINCT(friendId) FROM `user_friend`
                WHERE userId IN (SELECT DISTINCT(friendId) FROM `user_friend`
                                WHERE userId = $userID))

Upvotes: 0

Views: 134

Answers (2)

Adam
Adam

Reputation: 18807

This appears to be coherent.

The only thing you miss is that you should not return $userId which can be a friend of its friends' friends

EDIT : I don't know if your database already contains friendship in both way. So you also have to consider that if A is friend of B then B is friend of A and test for it.

So you have to play with a possible inversion in t2 like in the following

 SELECT t1.userId FROM user_friend t1
     JOIN user_friend t2 ON t1.friendId=t2.userId
     JOIN user_friend t3 ON t2.friendId=t3.userId
     WHERE t3.friendId=$userId AND t1.userId!=$userId
UNION
  SELECT t1.userId FROM user_friend t1
     JOIN user_friend t2 ON t1.friendId=t2.friendId
     JOIN user_friend t3 ON t2.userId=t3.userId
     WHERE t3.friendId=$userId AND t1.userId!=$userId;

Also, this includes friends of friends of friends, but not friends of friends.

Upvotes: 1

komodosp
komodosp

Reputation: 3616

Possibly easier using joins... 

SELECT DISTINCT u3.friendId FROM user_friend u1
   JOIN user_friend u2 ON u1.friendId = u2.userId
   JOIN user_friend u3 ON u2.friendId = u3.userId
   WHERE u1.userId = $userId

Upvotes: 2

Related Questions