php - 按共同好友数搜索 - 好友系统 Mysql PHP

Question

我正在我的论坛中创建一个好友系统。

我很难弄清楚如何根据 mutual_friend 计数吸引用户和订购。

我正在尝试构建一个显示推荐好友列表的页面。

这是我的表格结构:

users table
-----
user_id | name |

friends table
-----
friend_id | from_id | to_id

这是正在发生的事情的一个例子。

假设共有A,B,C,D,E,F = 站点中有 6 人。

• 我是A，B、C是我的 friend 。
• D 和 E 又是 B 的 friend 。
• D 也是 C 的友元，但 E 不是 C 的 friend 。
• F 不是站点中任何人的 friend 。

因此从上面的数据看来D 和E 是我的共同 friend (A)。 F 不是我的共同 friend 。

因为 D 是 B 和 C 的 friend ，而 E 是 < em>只有 B:

• A 和 D 有 2 个共同的 friend 。
• A 和 E 有 1 个共同的 friend 。
• A 和 F 有 0 个共同的 friend 。

现在如果我想搜索(记住我是 A)不是我 friend 的人，我可以这样做:

$myfriends = "2,3";//把我所有的 friend 放在一个变量中

SELECT * FROM users WHERE user_id NOT IN($myfriends)

但它会根据 user_id ASC 产生。

如何让它以 mutual_friends 的 DESC 顺序搜索。 ？

我是 A 即 user_id = 1

即有更多共同 friend 的人优先

谁能告诉我该怎么做？我被困在这里很长一段时间了。我搜索了很多东西，但无法弄明白。

Answer 1

此查询将考虑关系的互惠性，因此无论关系是“从 A 到 B”还是“从 B 到 A”都没有关系，它仍会返回预期的结果。所以给出这样的表:

CREATE TABLE people
    (`id` int, `name` varchar(1))
;

INSERT INTO people
    (`id`, `name`)
VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'C'),
    (4, 'D'),
    (5, 'E'),
    (6, 'F')
;


CREATE TABLE friends
    (`id` int, `personId1` int, `personId2` int)
;

INSERT INTO friends
    (`id`, `personId1`, `personId2`)
VALUES
    (1, 1, 2),
    (2, 3, 1),
    (3, 2, 4),
    (4, 5, 2),
    (5, 3, 4)
;

我相信这是按照你描述的那样建立的:A和B是 friend ，A和C是 friend (注意倒置关系)，B和D是 friend ，E和B是 friend (另一种倒置关系)，C和 D 是 friend 。

假设你想要的人的id在@personId中:

SELECT StrangerId, COUNT(MutualFriendId) AS TotalMutualFriends
FROM
(SELECT
  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
FROM
(SELECT 
  CASE 
    WHEN personId1 = @personId THEN personId2 
    ELSE personId1 
  END AS friendId 
FROM friends 
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;

@personId = 1 的结果是:

StrangerId  TotalMutualFriends
4           2
5           1

And here is a SQLFiddle演示(我无法让它允许我设置一个变量，所以它的位置有一个 1)。