时间戳

首页 > 解决方案 > 从具有父子关系的表中获取访问权限

sql - 从具有父子关系的表中获取访问权限

问题描述

我有以下架构来存储节点层次结构并访问角色的节点。除非角色在 accessdetails 表中有条目，否则角色从对父节点的访问集继承对子节点的访问权。

我想获取给定角色的特定节点的后代、级别和访问权限。在这种情况下，角色为 1，父级为“b”。

SQLfiddle链接

CREATE TABLE hierarchy
    (parent varchar(1), node varchar(1))
;

CREATE TABLE accessdetails
    (role integer, node varchar(1), access integer)
;
    
INSERT INTO hierarchy
    (parent, node)
VALUES
    (NULL, 'a'),
    ('a', 'b'),
    ('b', 'c'),
    ('c', 'p'),
    ('p', 'q'),
    ('q', 'r'),
    ('b', 'd'),
    ('d', 'j'),
    ('a', 'e'),
    ('e', 'f'),
    ('f', 'x')
;

/*
0-no,1-r,2-w,3-full
*/

insert into accessdetails
  (role, node, access)
values
  (1, 'b', 3),
  (1, 'c', 2),
  (1, 'p', 0)
 ;

以下查询正确返回级别和后代，但无法获得正确的访问权限。

with recursive
descendants as
  ( select parent, node as descendant, 1 as level,
     (select access from accessdetails where node=hierarchy.node and role=1) as access
    from hierarchy where parent = 'b'
  union all
    select d.parent, s.node, d.level + 1, 
   (select access from accessdetails where node=s.node and role=1) as access
    from descendants as d
      join hierarchy s
        on d.descendant = s.parent
  ) 
select descendant, level, access 
from descendants
order by parent, level, descendant ;

Current output:
descendant  level   access
c           1   2
d           1   (null)
j           2   (null)
p           2   0
q           3   (null)
r           4   (null)
Expected output:
descendant  level   access
c           1   2
d           1   3
j           2   3
p           2   0
q           3   0
r           4   0

我如何实现这一目标？

对架构稍作修改的最终解决方案：

标签： sqlpostgresqlcommon-table-expressionrecursive-query

解决方案

b这里的问题是，您以具有父节点的节点开始您的层次结构。这些是节点c和d。节点c具有2显式分配的访问权限，但节点d没有。该查询不会“看到”3分配给节点的访问权限，b因为该行不包含在查询中。

这个查询是我将如何解决这个问题。它生成整个层次结构，然后将其限制b为路径中第一个节点的行。

在这里提琴

with recursive descendants as ( 
  select h.parent, h.node as descendant, 1 as level, ad.access,
         array[h.node]::text[] as path
    from hierarchy h
         left join accessdetails ad on ad.node = h.node
  union all
    select d.parent, s.node, d.level + 1, 
           coalesce(ad.access, d.access), d.path||s.node::text
      from descendants as d
           join hierarchy s
             on d.descendant = s.parent
           left join accessdetails ad on ad.node = s.node
  ) 
select descendant, level, access, path
  from descendants
 where path[1] = 'b'
 order by parent, level, descendant ;

推荐阅读