I have a task, where I need to get all grandchild-child-parent-grandparent objects from one table.

The structure of my data:

That's mean:

• when I query object with ID=23 I would like to find 23,22,21
• When I query object with ID=22 I would like to find 23,22,21
• When I query object with ID=21 I would like to find 23,22,21

Here some peace of code that I did to get all parents and grandparents, but have difficulties with finding children and grandchildren

WITH RECURSIVE deepSearch AS (
SELECT 
    "t"."id",
    "t"."parentId"
FROM "Table" as t

UNION ALL

SELECT
    "parent"."id",
    "parent"."parentId",
FROM "Table" as parent
JOIN deepSearch AS child ON "child"."parentId" = "parent"."id"
)
SELECT * FROM deepSearch;

CodePudding user response：

one of the ways to solve this is with the similarity element that every branch of the tree will have, in this case the root branch.

assuming the following situation:

we can solve with the following query:

WITH RECURSIVE deepSearch AS (
SELECT 
    "t"."id" as root, # at this level all elements are root. because of the where clause
    "t"."id",
    "t"."parent_id"
FROM "Table" AS t
WHERE t.parent_id IS NULL # only root elements

UNION ALL
# recursive query
SELECT
    "parent".root,
    "child"."id",
    "child"."parent_id"
FROM "Table" as child
INNER JOIN deepSearch AS parent ON "child"."parent_id" = "parent"."id"
)
SELECT
    rootjoin.id,
    rootjoin.parent_id
FROM deepSearch
INNER JOIN deepSearch as rootjoin
    ON rootjoin.root = deepSearch.root
WHERE
    deepSearch.id = 23 # condition of your choice (22,21,11...)
;

CodePudding user response：

You cannot get what you are looking for directly is a query. The problem is when given an id you do not know where within the hierarchy that id resides: parent, root, or somewhere in between. There are basically 2 solutions:

1. Search up the tree, from the target, until you reach the root (here defined as parent being null) then down to the leaf. Alternatively, down to the leaf then back to the root.
2. Build a complete list of families then determine which contains the target.

Building a family is a fairly simple task with a recursive cte. Once built simply fine the family that contains the target. The following is one such implementation: (see demo)

create or replace function family_tree(id_of_interest integer) 
   returns integer[]
  language sql  
as $$
    with recursive deepsearch(parent, child, f) as 
         ( select t.id
                , t.parent_id
                , row_number() over()
             from test as t
             where t.parent_id is null 
           union all
          select t.id
               , t.parent_id
               , f
            from test t 
            join deepsearch d
              on (t.parent_id = d.parent)
        )
    select family 
      from ( select array_agg(parent) family
               from deepsearch
              group by f
           )  f
     where id_of_interest = any(family);
$$; 

The above can be implemented without defining a function. You just need to handpl passing the parameter.