我有一个自引用表用户:
id | follower
------------|------------
1 (adam) | 2 (bob)
1 (adam) | 3 (charlie)
2 (bob) | 1 (adam)
2 (bob) | 3 (charlie)
请注意,有循环引用。
我想得到一个用户的所有关注者,以及关注者的关注者,等等,这样所有的关注者都会显示在一个扁平的列表中,有他们各自的深度
亚当:
id | follower | depth
---|-------------|-------
1 | 1 (bob) | 0
2 | 3 (charlie) | 0
3 | 1 (adam) | 1 (bob -> adam)
4 | 3 (charlie) | 1 (bob -> charlie)
问题
我想避免第3行和第4行,这代表两个问题:
adam -> bob -> adam
,因为它是循环的。adam -> bob -> charlie
,因为charlie之前已经出现过。
通过在分支中保留访问的id
的path
列,我可以使用以下查询来解决问题#1
WITH RECURSIVE cte AS (
SELECT id, follower, 0 as depth, ARRAY[id] AS path
FROM user
UNION ALL
SELECT id, follower, depth + 1, id || path
FROM user
JOIN cte ON user.id = cte.follower
WHERE NOT path @> Array[user.id]
)
SELECT * from cte
但这并不能解决问题2。
它给出了以下结果:
follower | depth | path
------------|-------|-----
2 (bob) | 0 | {2}
3 (charlie) | 0 | {3}
3 (charlie) | 1 | {2, 3}
它仍然存在问题#2(重复的charlie
条目(,因为path
列只保留特定分支中的id
的列表。
如何解决问题#2?
可能的解决方案
我可以在我的代码(Node.JS(中通过保留全局缓存(相当于path
(来解决它。
const list = {}; /* <-- GLOBAL cache */
function recurse(user, depth = 0) {
for(const { id, followers } of user.followers) {
if (!(id in list)) {
list[id] = {id, depth}
recurse({ followers }, depth + 1);
}
}
}
然而,据我所知,上面的SQL查询相当于:
function recursive() {
const list = {}; /* <-- LOCAL cache */
for(const {id} of followers)
if (!(id in list)) ...
如何使用SQL中的全局缓存在代码中复制我的解决方案?
或者我可以通过任何其他方式达到预期的结果?
我正在使用Node.JS和PostgreSQL
如果我理解正确,您希望在递归搜索后每个跟随者只选择一行:
WITH RECURSIVE cte AS (
SELECT id, follower, 0 as depth, ARRAY[id] AS path
FROM user
UNION ALL
SELECT id, follower, depth + 1, id || path
FROM user
JOIN cte ON user.id = cte.follower
WHERE NOT path @> Array[user.id]
)
SELECT DISTINCT ON (follower) *
FROM cte
ORDER BY follower, depth;