2

I have the following tables:

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

I want to return all rows that the user has permission to see, a user has permission to an item if he has permission on the item or on one of its ancestors. for example:

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  |

According to the above data, the user has permissions to items (b, c, and d).

I want to create a query which joins the above tables and return all items that the user has permission to read, the expected result is:

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

There is an efficient way to search whether ltree contains other ltree? Or if ltree is-a-child of another entry? Something like *.a.* in lquery but between columns.

I tried to use <@/@> but they are only good for checks on root/item:

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
Michael
  • 896
  • 10
  • 28

1 Answers1

0

Please use below query,

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)));
Jim Macaulay
  • 4,709
  • 4
  • 28
  • 53
  • But the performance of such a query is awful, isn't it? no index can be used... – Michael May 27 '20 at 21:44
  • If your id and path is same, indexing will be helpful. Even in this scenario indexing on items will be useful as we are comparing a part of path from permissions table – Jim Macaulay May 28 '20 at 07:58
  • same = `last path node == id`? this is true. also, if I understand correctly, your method support only one child. in my (real) scenario a parent may have up to few(let's say 4) children (`a.b.c.d`). – Michael Jun 17 '20 at 17:50
  • It will support all the child. That the reason i took the substring of the parent and performing the join – Jim Macaulay Jun 18 '20 at 05:24