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?

Why would you want to do that? Just use the library to get the children and their grand children. — dan-klasson, Feb 24 '19 at 08:52
I checked it. But there is no method to get the grandchildren. What I want is to get a nodes at some depth of the subtree. What I mentioned is to get the children and grand children of the node which is basically 2 level from any of the nodes. — Aarthi, Feb 24 '19 at 09:00
It's right there in the docs: `root.children.first.children.first # => subchild` — dan-klasson, Feb 24 '19 at 09:06
I can get grandchild of any of the children of the desired node with the method you specified. But I can't get all the children of children. Anyways thanks for the response. — Aarthi, Feb 24 '19 at 17:36
`first` is just shorthand for fist element in the list. perhaps `descendants` would be of use too — dan-klasson, Feb 24 '19 at 18:01
But descendants will list all the nodes under a root of subtree. But I am not clear how to get the nodes with some level(say 2). Can you please elaborate? — Aarthi, Feb 24 '19 at 18:12
Root is where you want to start off. So you could fetch any node and then call `descendants`. Or you could do `root.first.descendants`. Or `root.last.children.first.descendants`. Etc, etc. Well, that's what I assume at least. — dan-klasson, Feb 24 '19 at 18:28

score 2 · Accepted Answer · answered Feb 24 '19 at 09:16

2

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)

answered Feb 24 '19 at 09:16

Vasfed

18,013
10
47
53

Getting the children, grand children in hierarchical structure

1 Answers1