SELECT查询返回好友到第5层

Question

我正试图实现社交网络问题从图数据库的书，但在SQL(使用PostgreSQL)。我有这两张表，用来定义一个人和他与另一个人的友谊。

CREATE TABLE IF NOT EXISTS Person(
ID INTEGER PRIMARY KEY,
Person VARCHAR(200)
);
CREATE TABLE IF NOT EXISTS PersonFriend(
PersonID INTEGER,
FriendID INTEGER,
PRIMARY KEY (PersonID, FriendID)
);

我要找的是一个查询，它将返回一个人的所有朋友，朋友的朋友，朋友的朋友的朋友等，直到第5级。

朋友图

我附上了一个我在Neo4j中制作的图形示例，它应该有助于可视化。我用Cypher很容易地解决了这个问题，但是我在SQL中做等价的事情时遇到了一些麻烦。使用Alice作为参考，我的查询应该返回Olivia, Zach, Bob, Mary, Caitlyn, Violet和Zerus，因为他们是她的(直接和间接)朋友，直到第5层。

这本书包含了这段代码，但它只到第二级(不返回第一级)。

SELECT p1.Person AS PERSON, p2.Person AS FRIEND_OF_FRIEND
FROM PersonFriend pf1 JOIN Person p1
ON pf1.PersonID = p1.ID
JOIN PersonFriend pf2
ON pf2.PersonID = pf1.FriendID
JOIN Person p2
ON pf2.FriendID = p2.ID
WHERE p1.Person = 'Alice' AND pf2.FriendID <> p1.ID

我将非常感谢任何解决这个问题的建议

Answer 1

递归查询是处理这个问题最简单的方法:

WITH RECURSIVE FriendCTE (PersonID, depth)
AS (
SELECT 1, 0  -- 1 being the ID of Alice
UNION
SELECT pf.FriendID, depth + 1
FROM FriendCTE cte
JOIN PersonFriend pf ON (cte.PersonID = pf.PersonID)
WHERE depth < 5
)
SELECT PersonID, Person
FROM FriendCTE cte
JOIN Person p ON (p.ID = cte.PersonID)
WHERE depth > 0
GROUP BY PersonID, Person;  -- to deal with loops
PersonID | Person
----------+---------
2 | Olivia
3 | Zach
4 | Bob
5 | Mary
6 | Caitlyn
7 | Violet
8 | Zerus
(7 rows)

旁注，您应该使用text类型，而不是VARCHAR(200)。