Relational database management systems based on SQL are only partially suitable for hierarchical or recursive queries. Other systems like ArangoDB are better suited for this (there was a great talk about this at GOTO 2016 ). However, it is also possible to create recursive queries in SQL using Common Table Expressions , allowing you, for example, to query classic parent/child tables for all ancestors and descendants.
In PostgreSQL, this would look like this:
0672c4761d202a35a9059fa137ace7c3
If you now want to get all children of a certain item_id, use the common table expressions:
0672c4761d202a35a9059fa137ace7c3
Also the query of the parents is no problem:
0672c4761d202a35a9059fa137ace7c3