Postgres ltree有一个子查询

Question

我有以下表格：

CREATE TABLE public.permissions (
    "user" varchar ,
    "action" permissions_action_enum, -- 'read'/'write'
    id ltree
);

CREATE TABLE public.items (
    "path" ltree NULL,
    id uuid NOT NULL,
);

我希望返回用户有权查看的所有行，如果用户对某项或其祖先之一具有权限，则该用户对该项具有权限。例如：

public.permissions:
user       |action|id |
-----------|------|---|
1          |read  |a  |
1          |read  |d  |


public.items:
path       |id |
-----------|---|
a.b        |b  |
a.c        |c  |
e.d        |d  |
g.f        |f  |

根据上述数据，用户具有对项(b、c和d)的权限。

我想创建一个连接以上表的查询，并返回用户拥有read权限的所有项，预期的结果是：

path       |id |
-----------|---|
a.b        |b  |
a.c        |c  |
e.d        |d  |

有没有一种有效的方法来搜索ltree是否包含其他ltree？或者如果ltree是另一个条目的子项？类似于lquery中的*.a.*，但在列之间。

我尝试使用<@/@>，但它们只适用于根目录/项目的检查：

select 'a.b'::ltree <@ 'b'::ltree -- false
select 'a.b'::ltree <@ 'a'::ltree -- true
select 'a.b'::ltree @> 'b'::ltree -- false
select 'a.b'::ltree @> 'a'::ltree -- false

Answer 1

请使用以下查询，

select i.path, i.id from items i
inner join permissions p
on ((p.id = substr(i.path, 0,1)) or (p.id = substr(i.path, instr(i.path,'.') +1,1)));