Getting the children, grand children in hierarchical structure

Question

I am working with hierarchical data model with rails and gem called ltree_hierarchy(https://github.com/cfabianski/ltree_hierarchy). In that each node will have a parent id(which is an immediate parent of the current node).

         1

     2       3

 4    5    6    7

the hierarchical structure is achieved using ltree extension of postgres. and in the gem ltree_hierarchy, the parent and path will be saved.

node       parent        path

  1         NULL         1

  2          1           1.2

  3          1           1.3

  4          2           1.2.4

  5          2           1.2.5

  6          3           1.3.6

  7          3           1.3.7

I can get the sibling, parent and children using the parent_id of the node. something like,

select * from table where parent_id = 1; # for getting the children of a node 1

select * from table where parent_id = 1 and id !=2; # for getting the sibling of a node 2

Is there any suggestion to get the children and grand children of a node in a single query?

Answer 1

Since you're using postgres' LTREE underneath - you can query it directly (see postgres documentation) like

 select * from table where path ~ '1234.*{1,2}'

(here 1234 is id of parent, and *{1,2} instructs to match at least one level and at most 2)