"people you may know" sql query

4
votes

I'm working on a "people you may know" feature. I have two tables:

USERS
id
email
name
etc

FRIENDSHIPS
user_id
friend_id

For each friendship I make two records. Say users 7 and 9 become friends... I would make a record where user_id=7,friend_id=9 and another where user_id=9, friend_id=7 in the friendships table.

How would I make a sql query that suggests people I'm likely to know based on friends of my friends? I also want it ordered based on the most mutual friends.

4
sqlmysqldatabasesqlite
if user 7 and 9 are friends, why do you want to have records storing the same data. I think you need to work on your table structure a little. Just thinking out loud, maybe a friendshipid column and two userid columns – pavanred
@Pavanred It's much easier to return a list of friends that way. I can say "select friend_id from friendships where user_id = 9"... Much easier than the alternatives. – tybro0103
So you are rebuilding facebook? :-) – Chris
@Chris lol... no, just private social networks – tybro0103

4 Answers

9
votes
select u.id, u.email, u.name, u.etc
-- Get all my friends
from Friendships as f1
-- Get their friends
inner join Friendships as f2
    on f1.friend_id = f2.user_id
-- Get their friends User information
inner join Users as u
    on f2.friend_id = u.id
where f1.user_id = @userId

Would be where I would start.

0
votes
Select friend_id from friendships where user_id IN (select friendid from freindships where userid = <userid>)

Considering user 7 and 9 are friends. For user 7, this query would give you the list of friends of user 9.

0
votes
select * from 
(select distinct friendsOfFriends.friends_id, count(1) as mutualfriends
  from FRIENDSHIPS friendsOfFriends
 inner join FRIENDSHIPS friends ON friends.friend_id = friendsOfFriends.user_id
  where friends.user_id = @myuserid
  group by friendsOfFriends.friends_id) t
order by mutualfriends desc
0
votes

You can use the query below;

Query:

Select * from [USERS] U where id not in 
((select F.friend_id as id from [FRIENDSHIPS] f where f.user_id='" + Session["UserId"] + "' and f.Status=1)
union (select F.user_id as id from [FRIENDSHIPS] f where f.friend_id='" + Session["UserId"] + "' and f.Status=1)
union (select F.friend_id as id from [FRIENDSHIPS] f where f.user_id='" + Session["UserId"] + "' and f.Status=0)
union (select F.friend_id as id from [FRIENDSHIPS] f where f.user_id='" + Session["UserId"] + "' and f.Status=2))
and U.id !='" + Session["UserId"] + "'

NOTE: Status may be "1", "0", "2" as a default parameters. The meaning of them are;

1: Confirmed 0: Not Confirmed yet 2: Denied

I wrote query code above considering by these parameters. Yours may be like this, too.

Take it easy!