Postgresql Recursive Self Join

Postgresql recursive self join

Here is the SQL using a recursive CTE:

with recursive tr(id1, id2, level) as (
      select t.id1, t.id2, 1 as level
      from t union all
      select t.id1, tr.id2, tr.level + 1
      from t join
           tr
           on t.id2 = tr.id1
     )
select *
from (select tr.*,
             max(level) over (partition by id1) as maxlevel
      from tr
     ) tr
where level = maxlevel;

Here is the SQLFiddle

Simple recursive SQL query

I think you can find your answer here:
Postgresql recursive self join

Then this website is quite good to explain stuff like that:
https://www.postgresqltutorial.com/postgresql-recursive-query/

Here is an example with an employee / subordinate model:

WITH RECURSIVE subordinates AS (
    SELECT
        employee_id,
        manager_id,
        full_name
    FROM
        employees
    WHERE
        employee_id = 2
    UNION
        SELECT
            e.employee_id,
            e.manager_id,
            e.full_name
        FROM
            employees e
        INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
    *
FROM
    subordinates;

Postgres self-join recursive CTE ancestry chain

The rows returned by a SQL query are in random order unless you specify an order by.

You can calculate depth by keeping track of it in the recursive CTE:

WITH RECURSIVE chain(from_id, to_id, depth) AS
    (
    SELECT  NULL::integer
    ,       1
    ,       1
    UNION
    SELECT  c.to_id
    ,       pb.previous_bill_id
    ,       depth + 1
    FROM chain c
    LEFT JOIN 
            pilates_bill pb
    ON      pb.bill_id = c.to_id
    WHERE   c.to_id IS NOT NULL
    )
SELECT  * 
FROM    chain
ORDER BY
        depth

PostgreSQL - Recursive CTE Self-Join for Manager Hierarchy

I would view this as a recursive CTE to get the hierarchy and then aggregation to create the jsonb value:

with recursive t as (
      select v.*
      from (values (3, 2, 0), (2, 1, 1), (1, null, 0)) v(employee_id, manager_employee_id, terminated_flag)
     ),
     cte as (
      select distinct employee_id, manager_employee_id, terminated_flag, 1 as lev
      from t
      union all
      select cte.employee_id, t.manager_employee_id, t.terminated_flag, lev + 1
      from cte join
           t
           on cte.manager_employee_id = t.employee_id
     )
select employee_id, jsonb_agg(jsonb_build_object('level', lev, 'id', manager_employee_id, 'terminated_flag', terminated_flag))
from cte
group by employee_id;

Here is a db<>fiddle.

Simplest way to do a recursive self-join?

WITH    q AS 
        (
        SELECT  *
        FROM    mytable
        WHERE   ParentID IS NULL -- this condition defines the ultimate ancestors in your chain, change it as appropriate
        UNION ALL
        SELECT  m.*
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.PersonID
        )
SELECT  *
FROM    q

By adding the ordering condition, you can preserve the tree order:

WITH    q AS 
        (
        SELECT  m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
        FROM    mytable m
        WHERE   ParentID IS NULL
        UNION ALL
        SELECT  m.*,  q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.PersonID
        )
SELECT  *
FROM    q
ORDER BY
        bc

By changing the ORDER BY condition you can change the ordering of the siblings.

SQL (postgres) - Recursive view for all parents of a child

Your main problem is that you're not joining against the recursive CTE (comcoltree). Therefore, you're never adding anything to the result set with the UNION. This is the best I have with the provided information:

WITH RECURSIVE comcoltree AS
(
    SELECT collection_id
         , community_id     AS any_community_id
      FROM community2collection
     WHERE collection_id = 'cbc98392-ba86-4ed3-b477-5a4a7652f3f0'
     UNION ALL
    SELECT prev.collection_id
         , next.parent_comm_id AS any_community_id
      FROM comcoltree prev
      JOIN community2community next ON prev.any_community_id = next.child_comm_id
)
SELECT DISTINCT 
       collection_id
     , any_community_id
  FROM comcoltree
;

Here is a DBFiddle to show it working. I threw the distinct in there because there were duplicates in the result set but I think that's due to having a duplicate in the sample data.

ae12f497-b7de-4cb6-939f-70968a5e7b0a 05810e9b-cf09-4c13-a750-297cca5fd84f

Postgres infinite self join

Hoping that this is your expected result. (I did something similar here: https://stackoverflow.com/a/52076212/3984221)

demo: db<>fiddle

Table comments:

id  body          user_id  likes  
--  ------------  -------  -----  
a   foo           1        1      
b   foofoo        1        232    
c   foofoofoo     1        23232  
d   fooFOO        1        53     
e   cookies       1        864    
f   bar           1        44     
g   barbar        1        54     
h   barBAR        1        222    
i   more cookies  1        1      

Table replies

id  parent_id  
--  ---------  
a   (null)     
b   a          
c   b          
d   a          
e   (null)     
f   (null)     
g   f          
h   f          
i   (null)     

Result:

{
    "comments": [{
        "children": [],
        "username": "Mike Tyson",
        "comment_id": "i",
        "comment_body": "more cookies",
        "comment_likes": 1
    },
    {
        "children": [{
            "children": [],
            "username": "Mike Tyson",
            "comment_id": "b",
            "comment_body": "foofoo",
            "comment_likes": 232
        },
        {
            "children": [{
                "children": [],
                "username": "Mike Tyson",
                "comment_id": "c",
                "comment_body": "foofoofoo",
                "comment_likes": 23232
            }],
            "username": "Mike Tyson",
            "comment_id": "d",
            "comment_body": "fooFOO",
            "comment_likes": 53
        }],
        "username": "Mike Tyson",
        "comment_id": "a",
        "comment_body": "foo",
        "comment_likes": 1
    },
    {
        "children": [],
        "username": "Mike Tyson",
        "comment_id": "e",
        "comment_body": "cookies",
        "comment_likes": 864
    },
    {
        "children": [{
            "children": [],
            "username": "Mike Tyson",
            "comment_id": "g",
            "comment_body": "barbar",
            "comment_likes": 54
        },
        {
            "children": [],
            "username": "Mike Tyson",
            "comment_id": "h",
            "comment_body": "barBAR",
            "comment_likes": 222
        }],
        "username": "Mike Tyson",
        "comment_id": "f",
        "comment_body": "bar",
        "comment_likes": 44
    }]
}

Query:

Recursion:

WITH RECURSIVE parent_tree AS (
    SELECT 
        id, 
        NULL::text[] as parent_id,
        array_append('{comments}'::text[], (row_number() OVER ())::text) as path, 
        rc.children  
    FROM replies r
    LEFT JOIN LATERAL (SELECT parent_id, ARRAY_AGG(id) as children FROM replies WHERE parent_id = r.id GROUP BY parent_id) rc ON rc.parent_id = r.id
    WHERE r.parent_id IS NULL 

    UNION

    SELECT 
        r.id, 
        array_append(pt.parent_id, r.parent_id), 
        array_append(array_append(pt.path, 'children'), (row_number() OVER (PARTITION BY pt.parent_id))::text),
        rc.children      
    FROM parent_tree pt
    JOIN replies r ON r.id = ANY(pt.children)
    LEFT JOIN LATERAL (SELECT parent_id, ARRAY_AGG(id) as children FROM replies WHERE parent_id = r.id GROUP BY parent_id) rc ON rc.parent_id = r.id
), json_objects AS (
   SELECT c.id, jsonb_build_object('children', '[]'::jsonb, 'comment_id', c.id, 'username', u.name, 'comment_body', c.body, 'comment_likes', c.likes) as jsondata
   FROM comments c
   LEFT JOIN users u ON u.id = c.user_id
)
SELECT 
    parent_id, 
    path,
    jsondata
FROM parent_tree pt 
LEFT JOIN json_objects jo ON pt.id = jo.id
ORDER BY parent_id NULLS FIRST

The only recursion part is within CTE parent_tree. Here I am searching for the parents and build a path. This path is needed for inserting the json data later at the right position.

The second CTE (json_objects) builds a json object for each comment with an empty children array where the children can be inserted later.

The LATERAL join searches the replies table for children of the current id and gives an array with their ids.

The ORDER BY clause at the end is important. With this it is ensured that all upper nodes come before the lower nodes (their children). Otherwise the input into the global json object could fail later because a necessary parent could not exist at the right moment.

Building the final JSON object:

CREATE OR REPLACE FUNCTION json_tree() RETURNS jsonb AS $$
DECLARE
    _json_output jsonb;
    _temprow record;
BEGIN

    SELECT 
        jsonb_build_object('comments', '[]'::jsonb) 
    INTO _json_output;

    FOR _temprow IN
        -- <query above>
    LOOP
        SELECT jsonb_insert(_json_output, _temprow.path, _temprow.jsondata) INTO _json_output;
    END LOOP;

    RETURN _json_output;
END;
$$ LANGUAGE plpgsql;

It is not possible to build the json object within the recursion because within the query the jsondata object is not a global variable. So if I would add b as child into a in one recursion branch, it wouldn't exist in another branch where I would add c as child.

So it is necessary to generate a global variable. This could be done in a function. With the calculated path and child objects it is really simple to build the final json together: looping through the result set and add the json object into the path of the global object.

self join table to get hierarchy for specific root node

You want to use a recursive CTE to do to this. They are complicated to wrap your head around, but they are very common solution for a parent/child hierarchy table.

In your case it would look something like:

WITH recCTE AS 
(
   /* Here we select the starting point for the recursion */
   SELECT Id, ParentId FROM yourtable WHERE Id = 0
   UNION ALL
   /* Here we select how the recursive result set joins to 
    * to the original table
    * 
    * This will run over and over again until the join fails
    * appending records to the recCTE result set as it goes.
    */
   SELECT 
      yourtable.Id,
      yourtable.ParentId
   FROM recCTE
      INNER JOIN yourtable 
          ON recCTE.Id = yourtable.ParentId
)
SELECT * FROM recCTE;

How can I stop my Postgres recusive CTE from indefinitely looping?

In catalog_listings table listing_name and parent_name is same for child1 and child2
In relator table parent_name and child_name is also same for child1 and child2

These rows are creating cycling recursion.

Just remove those two rows from both the tables:

delete from catalog_listings where id in (4,5)
delete from relator where id in (7,8)

Then your desired output will be as below:

---