MySQL Query to find friends and the number of common friends

Question

I looked through the questions, but I can’t find anything that does exactly what I need, and I can’t figure out how to do it myself.

I have 2 tables, a user table and a friends link table. User table - table of all my users:

+---------+------------+---------+---------------+ | user_id | first_name | surname | email | +---------+------------+---------+---------------+ 1 joe bloggs [e-mail] 2 bill bloggs [e-mail] 3 john bloggs [e-mail] 4 karl bloggs [e-mail] 

My friends link table displays all the relationships between users, for example:

  +--------=+---------+-----------+--------+ | link_id | user_id | friend_id | status | +---------+---------+-----------+--------+ 1 1 3 a 2 3 1 a 3 4 3 a 4 3 4 a 5 2 3 a 6 3 2 a 

As a note, a in the status column means approved, there may also be r (request) and d (rejected).

What I want to do is a query where, if a user searches, he will return a list of users with whom they are currently not familiar, and how many friends they have in common with each other.

I managed to get a request for all users who are currently not familiar with them. Therefore, if the user performing the search had user ID 1:

 SELECT u.user_id,u.first_name,u.surname FROM users u LEFT JOIN friend_links fl ON u.user_id = fl.user_id AND 1 IN (fl.friend_id) WHERE fl.friend_id IS NULL AND u.user_id != 1 AND surname LIKE 'bloggs' 

How then do I have an account for the number of common friends for each returned user?

EDIT:

As well as editing, because I do not think that I particularly clearly understand my question.

In the query that I have above, the following result set will be created:

  +---------+------------+---------+ | user_id | first_name | surname | +---------+------------+---------+ 2 bill bloggs 4 karl bloggs 

These are users matching the surnames of bloggs who are currently not familiar with joe bloggs (user id 1).

Then I want to have how many common friends each of these users has with the user performing the search so that the returned results look like this:

  +---------+------------+---------+--------+ | user_id | first_name | surname | mutual | +---------+------------+---------+--------+ 2 bill bloggs 1 4 karl bloggs 1 

Each of these returned users has 1 mutual friend as joe bloggs (user ID 1), who is friends with john bloggs, and john bloggs is friends with both returned users.

Hope this will be a little more clear.

Thanks.

Answer 1

This query lists all those who are not friends with user 1, and whose last name matches '%bloggs%' :

 SELECT users.user_id, users.first_name, users.surname, Sum(IF(users.user_id = friend_links_1.friend_id, 1, 0)) As mutual FROM users inner join (friend_links INNER JOIN friend_links friend_links_1 ON friend_links.friend_id = friend_links_1.user_id) ON friend_links.user_id=1 AND users.user_id<>1 WHERE users.surname LIKE '%bloggs%' GROUP BY users.user_id, users.first_name, users.surname HAVING Sum(IF(users.user_id = friend_links.friend_id, 1, 0))=0 

just change the user id in the ON clause and the last name in the WHERE clause. I think that now it should work correctly!

Answer 2

Mutual friends can be found by attaching the friend_links table to themselves in the friend_id field as follows:

 SELECT * FROM friend_links f1 INNER JOIN friend_links f2 ON f1.friend_id = f2.friend_id WHERE f1.user_id = $person1 AND f2.user_id = $person2 

But keep in mind that this is in the worst case, essentially squaring the number of rows in the friend_links table and can quite easily raise your server if you have a non-trivial number of rows. The best option would be to use 2 subqueries for each user, and then join their results.

 SELECT * FROM ( SELECT * FROM friend_links WHERE user_id = $person1 ) p1 INNER JOIN ( SELECT * FROM friend_links WHERE user_id = $person1 ) p2 ON p1.friend_id = p2.friend_id 

In addition, you can simplify the friend_links table by deleting the surrogate key link_id and simply making (user_id,friend_id) primary key, since they must be unique anyway.

---

Edit:

How will this be applied to the original search query for users who are not friends yet, I would like to do as in a single query, if possible?

 SELECT f2.user_id, COUNT(*) 'friends_in_common' FROM friend_links f1 LEFT JOIN friend_links f2 ON f1.friend_id = f2.friend_id WHERE f1.user_id = $person GROUP BY f2.user_id ORDER BY friends_in_common DESC LIMIT $number 

I also think that user_id constraints can be migrated from the WHERE to JOIN clauses to reduce the size of the dataset created by the self-join and eliminate the use of subqueries, as in my second example.

Answer 3

If A is a friend of B, then B is also a friend of A? Wouldn't it be better to use only a link instead of two links (and instead of two lines in friends_links)? Then you need to use two status columns, status1 and status2, and A is a friend of B only if status1 = status2 = "a".

There are many ways to show mutual friends, for example:

 SELECT friend_id FROM friend_links WHERE friend_links.user_id = $user1 or friend_links.user_id = $user2 AND NOT (friend_links.friend_id = $user1 or friend_links.friend_id = $user2) GROUP BY friend_id HAVING Count(*)>1 

And this request is displayed for each user and anyone who is not his friend:

 SELECT users.user_id, users.first_name, users_1.user_id, users_1.first_name FROM users INNER JOIN users users_1 ON users.user_id <> users_1.user_id WHERE NOT EXISTS (SELECT * FROM friend_links WHERE friend_links.user_id = users.user_id AND friend_links.friend_id = users_1.user_id) 

(The only thing I did not check is the status of friendship, but it is easy to add this check).

I am still working on this, but it is not easy to combine these two togheter requests. So this is not quite the answer, I am just showing some ideas that I have tried.

But what do you need exactly? A query that returns each user with someone who is not his friend and number of friends, or user_id is already specified?

With some code, this is not a problem to answer your question ... but there should be a good way just using SQL! :)

EDIT:

I'm still wondering if there is a better solution for this, in particular, the following query may be very slow, but it looks like this might work:

 SELECT users_1.user_id, users_2.user_id, Sum(IF(users_1.user_id = friend_links.user_id AND users_2.user_id = friend_links_1.friend_id, 1, 0)) As CommonFriend FROM users users_1 INNER JOIN users users_2 ON users_1.user_id <> users_2.user_id, (friend_links INNER JOIN friend_links friend_links_1 ON friend_links.friend_id = friend_links_1.user_id) GROUP BY users_1.user_id, users_2.user_id HAVING Sum(IF(users_1.user_id = friend_links.user_id AND users_2.user_id = friend_links.friend_id, 1, 0))=0 

(as before, I did not check the status of friendship)

If the user is specified, you can put WHERE users_1.user_id=$user1 , but it’s better to just leave one user table and filter the next INNER JOIN with that user.