关注朋友的朋友建议MySQL

Question

I'm creating a 'Follow Suggestion' feature, where I'd like to show random Friends of my Friends who's NOT my friend.

user_friends table

friend_id | friend_one | friend_two | role

user table

uiD | username 

For each friend that a user follow it makes two records. If user 1 and 2 become friends a record would be created where, friend_one = 1 & friend_two = 2, friend_id = ramdom AUTO_INCREMENT number role = fri in the user_friends table.

User table, just has the users id and username.

How would I make a sql query that suggests "who to follow" based on friends of my friends ? Just how twitter does it.

What I've tried hasn't quite worked which does not seem to make much sense even to me. http://pastebin.com/tCt6jdAZ <- Query code. ( Don't want to post here because feels useless ).

Answer 1

If I understand your question correct:

you need to join your 'user_friends' table twice:

select * from 
   user_friends level1,
   user_friends level2
where 
   level1.friend_two = level2.friend_one -- or opposite i am not sure if a understood your model
    and level1.friend_one = 'starting friend id'