sql - 递归 SQL 查找喜欢的层次结构(带潜在圆圈)

Question

我有一个点赞表(uid1 点赞 uid2)并给定了一个特定的用户 ID (uid) 我需要找到所有喜欢他的人，或者任何喜欢他的人，等等等等。

with recursive Hierarchy(uid, Level)
as
(
    select 
        uid1 as uid, 1 as Level
   from 
    Likes l
   where 
    l.uid2 = 1 --parameter will go here
    union all
    select  
        l.uid1, lh.Level + 1
    from 
        Likes l   
    inner join Hierarchy lh
        on l.uid2 = lh.uid
    where l.uid1 not in (select uid from Hierarchy) --this is wrong syntax in postgresql
)

select * from Hierarchy

例如在表 Likes 中给出以下值时会出现问题

2,1 (2 likes 1)
3,1 (3 likes 1)
4,1 (4 likes 1, 1 is popular)
3,4 (3 likes 4)
4,3 (4 likes 3)

点赞层次结构中有一个圆圈，我只想添加不是上一次迭代的项目(因此 NOT IN)。

那么是否可以插入一个限制，仅用于添加新的 uid？

Answer 1

基于这个模板:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[g.id],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || g.id,
          g.id = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

(http://www.postgresql.org/docs/8.4/static/queries-with.html)

你得到:

with recursive Hierarchy(uid, Level, path, cycle)
as
(
    select 
        uid1 as uid, 1 as Level, ARRAY[l.uid], false
   from 
    Likes l
   where 
    l.uid2 = 1 --parameter will go here
    union all
    select  
        l.uid1, lh.Level + 1, 
        path || l.uid,
        l.uid = ANY( path )
    from 
        Likes l   
    inner join Hierarchy lh
        on l.uid2 = lh.uid
)

select * from Hierarchy