I am creating a Friend System in my Forum.
I am having a tough time trying to figure out how I would grab users and order by the mutual_friend count.
I am trying to build a page that shows a list of recommended friends.
Here is my structure of tables:
user_id | name |
friend_id | from_id | to_id
Here is an example of what is happening.
Suppose there are total of
F = 6 people in the site.
- I am
C are my friends.
E in turn are friends of
D is also a friend of
E is not a friend of
F is not a friend of anyone in the site.
Therefore from above data it looks like
E are mutual friends of me (
F is not a mutual friend of mine.
D is a friend of both
E is friend of only
2 mutual friends.
1 mutual friend.
0 mutual friend.
Now if I want to search (remember i am
A) for people who are not my friends I can do something like:
$myfriends = "2,3"; //putting all my friends in a variable
SELECT * FROM users WHERE user_id NOT IN( $myfriends )
But it will yield in terms of
user_id ASC .
How can I make it search in
DESC order of
user_id = 1
i.e. Person with more mutual friends comes first
Please can anyone show me how can I do this? I have been stuck here for a long while. I searched for lots of thing but can't figure it out.
Best How To :
This query will take the reciprocity of relationships into account, so it doesn't matter if the relationship goes "From A to B" or "From B to A", it will still return the expected result. So given tables like this:
CREATE TABLE people
(`id` int, `name` varchar(1))
INSERT INTO people
CREATE TABLE friends
(`id` int, `personId1` int, `personId2` int)
INSERT INTO friends
(`id`, `personId1`, `personId2`)
(1, 1, 2),
(2, 3, 1),
(3, 2, 4),
(4, 5, 2),
(5, 3, 4)
I believe this is set up as you described: A and B are friends, A and C are friends (notice the inverted relationship), B and D are friends, E and B are friends (another inverted relationship), and C and D are friends.
Assume the id of the person you want is in @personId:
SELECT StrangerId, COUNT(MutualFriendId) AS TotalMutualFriends
CASE WHEN f.personId2 = mf.friendId THEN f.personId1 ELSE f.personId2 END AS StrangerId,
CASE WHEN f.personId1 = mf.friendId THEN f.personId1 ELSE f.personId2 END AS MutualFriendId
WHEN personId1 = @personId THEN personId2
END AS friendId
WHERE personId1 = @personId OR personId2 = @personId) AS mf
INNER JOIN friends f
ON (personId1 != @personId AND personId2 = mf.friendId)
OR (personId1 = mf.friendId AND personId2 != @personId)
) AS totals
GROUP BY StrangerId
ORDER BY TotalMutualFriends DESC;
Results for @personId = 1 are:
And here is a SQLFiddle to demonstrate (I couldn't get it to allow me to set up a variable, so there is a 1 in its place).