php - 确定两个用户是否通过 friend 的 friend 建立联系

Question

所以有一个friendship_request表:

+--------+----------+
| sender | receiver |
+--------+----------+
|      1 |        2 |
|      2 |        1 |
+--------+----------+

如果两个用户互相发送了请求，则两个用户就是 friend 。

我使用 PHP 的 array_intersect 和包含每个用户的所有 friend 的数组来确定他们是否通过 friend 的 friend 进行连接。

即

1 <--> 2 <--> 3

查找两个用户的 friend 之间是否有互为 friend 的 friend 的最有效方法是什么。即

+--------+----------+
| sender | receiver |
+--------+----------+
|      1 |        2 |
|      2 |        1 |
|      2 |        3 |
|      3 |        2 |
|      3 |        4 |
|      4 |        3 |
+--------+----------+


1 <--> 2 <--> 3 <--> 4

用户 1 应该知道他与用户 4 的关系。

PS:用PHP/伪代码或者MySQL都可以

编辑:我不想创建另一个表或 View 。我想利用上述资源获得最佳解决方案。

Answer 1

首先创建检测友谊的 View :

CREATE VIEW friendship (friend1, friend2) 
AS SELECT p1.id, p2.id from person as p1, person as p2
WHERE 
(SELECT count(*) from friendship_request as fr1 WHERE 
fr1.sender = p1.id AND fr1.receiver = p2.id) > 0 
AND 
(SELECT count(*) from friendship_request as fr2 WHERE 
fr2.receiver = p1.id AND fr1.sender = p2.id) > 0 

现在查询第一级连接就像这样简单

SELECT p1.name, p1st.name, p2.name 
FROM person as p1, person as p2, person as p1st, 
friendship as fs1, friendship as fs2 
WHERE p1.id = fs1.friend1 
AND p2.id = fs2.friend1 
AND fs1.frind2 = fs2.frind2 
AND fs2.frind2 = p1st.id 

对于二级连接:

SELECT DISTINCT p1.name, p2nd1.name, p2nd2.name, p2.name 
FROM person as p1, person as p2, person as p2nd1, person as p2nd2, 
friendship as fs1, friendship as fs2, friendship as fs2nd
WHERE  p1.id = fs1.friend1 
AND p2.id = fs2.friend1 
AND fs1.frind2 = fs2nd.frind2 
AND fs2.frind2 = fs2nd.frinend2 
AND p2nd1.id = fs1.frind2 
AND p2nd2.id = fs2.frind2

等等。

没有测试，但在任何正常的 RDBMS 中应该可以工作:-)