选择查询以将朋友返回到第五级

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)
);

我正在寻找的是一个查询，它将归还所有人的朋友，朋友的朋友，朋友的朋友等人，直到一个人的第五级。

朋友图

我已经连接了我在neo4j中制作的图形示例，该示例应该有助于可视化。我使用Cypher轻松解决了它，但是我在SQL中遇到了一些麻烦。使用爱丽丝作为参考，我的疑问应该将奥利维亚，扎克，鲍勃，玛丽，凯特琳，紫罗兰和Zerus返回，因为他们是她（直接和间接）的朋友，直到第五级。

该书包括此代码段，但仅升至第二级（并且不返回第一个）。

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)

SideNote，您应该使用text类型而不是varchar（200）。