我正在我的论坛中创建一个朋友系统。
我很难弄清楚如何按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
是我的朋友。 -
D
和E
反过来又是B
的朋友。 -
D
也是C
的朋友,但E
不是C
的朋友。 -
F
不是网站上任何人的朋友。
因此,从上面的数据来看,D
和E
是我共同的朋友(A
)。 F
不是我共同的朋友。
由于D
是B
和C
的朋友,而E
只是B
的朋友:
-
A
和D
2
共同的朋友。 -
A
和E
1
共同的朋友。 -
A
和F
0
共同的朋友。
现在,如果我想搜索(记住我是A
)不是我朋友的人,我可以做这样的事情:
$myfriends = "2,3"; //putting all my friends in a variable
SELECT * FROM users WHERE user_id NOT IN( $myfriends )
但它会在user_id ASC
方面产生.
如何使其按mutual_friends
顺序搜索DESC
?
我A
即user_id = 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是朋友,A和C是朋友(注意倒置关系),B和D是朋友,E和B是朋友(另一个倒置关系),C和D是朋友。
假设所需人员的 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
这里有一个 SQLFiddle 要演示(我无法让它允许我设置一个变量,所以有一个 1 代替它)。
也许是这样的:
Select user_id, friends.to_id, count(friend_of_friend.to_id)
from users left outer join
friends on users.user_id = friends.from_id left outer join
users as friend_user on friends.to_id = friend_user.user_id left outer join
friends as friend_of_friend on friend_user.user_id = friend_of_friend.from_id and friend_of_friend.to_id in (select to_id from friends where from_id = users.user_id)
Group by USER_ID, friends.to_id
Order by 3
为清楚起见,请编辑:此查询的逻辑取决于多次联接同一表。前两个联接非常简单,我们从一个用户表开始,然后加入将每个用户与其所有朋友链接的好友表。但是随后我们再次加入用户表,但这次使用"to"列 - 我们获取每个朋友的用户信息。由于我们不能在查询中两次使用相同的表名,因此我们为其指定别名"friend_user"。然后我们根据friend_user表中的 id 再次加入 friends 表 - 这为我们提供了每个原始用户的朋友的所有朋友。然后,我们使用"Friend_of_friend.to_id in ..."来限制我们返回的朋友的好友将朋友的朋友与原始用户的所有朋友的列表进行比较,我们通过子查询引入 - 括在括号中的"in"后面的部分。