MySQL查询来查找朋友和共同朋友的数量 [英] MySQL Query to find friends and number of mutual friends
问题描述
我已经仔细研究了所有问题，但找不到符合我需要的任何东西，也无法自己弄清楚该怎么做.
I have looked through the questions but I cant find anything that does exactly what I need and I can't figure out how to do it myself.
我有2个表，一个用户表和一个朋友链接表.用户表是我所有用户的表:
I have 2 tables, a user table and a friend link table. The user table is a table of all my users:
+---------+------------+---------+---------------+
| user_id | first_name | surname | email |
+---------+------------+---------+---------------+
1 joe bloggs joe@test.com
2 bill bloggs bill@test.com
3 john bloggs john@test.com
4 karl bloggs karl@test.com
然后，我的朋友链接表显示用户之间的所有关系，例如:
My friend links table then shows all relationships between the 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
请注意，状态列中的a表示已批准，也可能有r(请求)和d(拒绝).
As a note the a in the status column means approved, there could also be r(request) and d(declined).
我想做的是查询一个查询，如果用户进行搜索，它将带回他们当前尚未与之成为好友的用户列表，以及每个用户与之有多少个共同的朋友.
What I want to do is have a query where if a user does a search it will bring back a list of users that they are currently not already friends with and how many mutual friends each user has with them.
我设法对当前不是他们的朋友的所有用户进行查询.因此，如果进行搜索的用户的用户ID为1:
I have managed to get a query for all users that are currently not friends with them. So if the user doing the search had the user id of 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 a count of the number of mutual friends for each returned user?
只是编辑，因为我认为我对我的问题并不特别清楚.
Just as an edit as I don't think I am being particularly clear with my question.
我目前在上面的查询将产生以下结果集:
The query that I currently have above will produce the following set of results:
+---------+------------+---------+
| user_id | first_name | surname |
+---------+------------+---------+
2 bill bloggs
4 karl bloggs
与当前不是joe blogg朋友的姓博客匹配的用户(用户ID 1).
Those are the users matching the surname bloggs that are not currently friends with joe bloggs (user id 1).
然后，我想让这些用户中的每个用户与进行搜索的用户有多少个共同的朋友，以便返回的结果如下所示:
Then I want to have how many mutual friends each of these users has with the user doing the search so the returned results would look like:
+---------+------------+---------+--------+
| user_id | first_name | surname | mutual |
+---------+------------+---------+--------+
2 bill bloggs 1
4 karl bloggs 1
这些返回的用户中的每个都有1个共同的朋友，因为joe bloggs(用户ID 1)是john bloggs的朋友，而john bloggs是两个返回用户的朋友.
Each of these returned users has 1 mutual friend as joe bloggs (user id 1) is friends with john bloggs and john bloggs is friends with both returned users.
我希望这一点更加清楚.
I hope this is a bit more clear.
谢谢.
推荐答案
此查询列出与用户1不是朋友且姓氏与'%bloggs%'匹配的任何人:
This query lists anyone who's not friend with user 1 and whose surname 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
只需在ON子句上更改用户ID，并在WHERE子句上更改姓.我认为它现在应该可以正常工作！
just change the user id on the ON clause, and the surname on the WHERE clause. I think it should work correctly now!
这篇关于MySQL查询来查找朋友和共同朋友的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
