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?
Working with type
ltree
Turns out you are working with the additional module
ltree
. And the dot (.
) is your unambiguous delimiter. Assumingpath
is typeltree
(you didn't clarify, yet) simplifies the task. There are dedicated operators like:So:
ltree
also provides operator classes for a GiST index:.. 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 onpath
instead:About the
lquery
type.db<>fiddle here
Only needs the GiST index.
Working with type
text
While working with
text
instead ofltree
(you didn't clarify, yet), here is one of many ways:db<>fiddle here
The subquery
t1
splitspath
into its building blocks. Then join to another instance oftbl_txt
.