By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
Id INTEGER NOT NULL PRIMARY KEY
,User_id INTEGER NOT NULL
,type VARCHAR(16) NOT NULL
,content VARCHAR(7) NOT NULL
,item_id INTEGER NOT NULL
,secondary_item_id INTEGER NOT NULL
);
INSERT INTO mytable(Id,User_id,type,content,item_id,secondary_item_id) VALUES (879,1,'activity_comment','Amazing',833,833);
INSERT INTO mytable(Id,User_id,type,content,item_id,secondary_item_id) VALUES (907,168,'activity_comment','Great',833,879);
INSERT INTO mytable(Id,User_id,type,content,item_id,secondary_item_id) VALUES (908,1,'activity_comment','Welcome',833,907);
with recursive cte as (
select t.*, 1 lvl
from mytable t
where
type ='activity_comment'
and item_id = 833
and secondary_item_id = item_id
union all
select t.*, c.lvl + 1
from mytable t
inner join cte c on c.id = t.secondary_item_id
where t.type = 'activity_comment'
)
select * from cte order by lvl
| Id | User_id | type | content | item_id | secondary_item_id | lvl |
|---|---|---|---|---|---|---|
| 879 | 1 | activity_comment | Amazing | 833 | 833 | 1 |
| 907 | 168 | activity_comment | Great | 833 | 879 | 2 |
| 908 | 1 | activity_comment | Welcome | 833 | 907 | 3 |