How to filter by path with PostgreSQL

Question

I have some resources in my database that are inherited to their sub-resources. I need to be able to get also the inherited resources when I query a resource. I have a field called path which I'm planning to use. The path always contains the full path of all resources related to the resource we are currently handling.

Example:

+-----------------------------------------+
| id | res_id    | path                   |
|-----------------------------------------|
| 1  | res_1     | res_1                  |
| 2  | res_1.1   | res_1.res_1.1          |
| 3  | res_1.2   | res_1.res_1.2          |
| 4  | res_1.1.1 | res_1.res_1.1.res_1.1.1|
+-----------------------------------------+

If I query the res_1.1, I'd also have to get the res_1 because it is the parent of res_1.1. And if I get the res_1.1.1, I'd have to get also rows 1 and 2 because they are included in the path of the res_1.1.1. Would love some advice how to do this with Postgres. I'm also using sqlmodel to write queries if that's important information.

EDIT. My apologies for the vague introduction, the parameter path is already a sqlalchemy Ltree -field. I hope this makes things a bit simpler?

Answer 1

Working with type ltree

Turns out you are working with the additional module ltree. And the dot (.) is your unambiguous delimiter. Assuming path is type ltree (you didn't clarify, yet) simplifies the task. There are dedicated operators like:

ltree @> ltree → boolean

Is left argument an ancestor of right (or equal)?

So:

SELECT t.*
FROM   tbl_ltree t1   
JOIN   tbl_ltree t ON t.path @> t1.path
WHERE  t1.res_id = 'res_1_1_1';  -- your search term here

ltree also provides operator classes for a GiST index:

CREATE INDEX tbl_ltree_path_gist_idx ON tbl USING GIST (path);

.. which can be used by the above query. Plus, you'd have another B-tree index on res_id.

res_id is completely redundant and can be ignored or even dropped. We can work with another ltree operator on path instead:

ltree ~ lquery → boolean

Does ltree match lquery?

About the lquery type.

SELECT *
FROM   tbl_ltree   
WHERE  path @> (SELECT path FROM tbl_ltree WHERE path ~ '*.res_1_1_1'::lquery);

db<>fiddle here

Only needs the GiST index.

Working with type text

While working with text instead of ltree (you didn't clarify, yet), here is one of many ways:

SELECT t.*
FROM  (
   SELECT unnest(string_to_array(path, '.')) AS res_id
   FROM   tbl_txt
   WHERE  res_id = 'res_1_1_1'  -- your search term here
   ) t1
JOIN  tbl_txt t USING (res_id);

db<>fiddle here

The subquery t1 splits path into its building blocks. Then join to another instance of tbl_txt.

Answer 2

For example, using the LIKE predicate to find a row that partially matches path. The query is:

SELECT t1.* FROM resources t1
WHERE
  (
    SELECT t2.path FROM resources t2
    WHERE t2.res_id = 'res_1.1'
  )
  LIKE t1.path||'%';

If path does not match correctly, add ..

SELECT t1.* FROM resources t1
WHERE
  (
    SELECT t2.path||'.' FROM resources t2
    WHERE t2.res_id = 'res_1.1'
  )
  LIKE t1.path||'.%';

DB Fiddle