Find record in closure table based on path

Question

I'm using a closure table like the one found in this answer (great answer btw):

hierarchical/tree database for directories in filesystem

Do you know if it's possible to get a record with a single query, based on a path string?

The path string would be the column name separated by /. For example, ROOT/Dir2/Dir4/Dir7 from that question should get me the Dir7 record.

Answer 1

In its basic form, the closure table just contains all pairs that are connected transitively.

To answer your query without recursion, the concatenated path string must be there already.

Do you need to select paths starting from the root node only?

The you could follow one of the following two options:

1. Store the absolute path as an additional column in your nodes table.
2. Or directly usse the absolute paths as key of your nodes, hence they are also the foreign keys in the closure table, and you can directly select them.

Do you need to select relatively to some starting node?

In that case, you could just store the relative path for each pair in the closure table, e.g., making it closure(ancestor_id int, descendant_id int, relative_path_between varchar). The INSERT operations used to build up the closure tables can be extended easily by the respective concat expression, e.g., to insert a new child Y for parent X with relative path Z, you would do

insert into closure_table(ancestor_id, descendant_id, relative_path_between)
select ancestor_id, 
       <Y>, 
       relative_path_between || '/' || <Z>
from closure_table 
where descendant_id = <X>
union all
select <Y>,<Y>,'';

(cf Bill Karwin's slides, slides 68 -- 78)