How to Find All Ids of Children Recursively

Get all children by parent id recursively (php)

function getRecursiveChildren($id, $items): array
{
    $kids = [];
    foreach ($items as $key => $item) {
        if ($item['parent_id'] === $id) {
            $kids[] = $key;
            array_push($kids, ...getRecursiveChildren($key, $items));
        }
    }
    return $kids;
}

if order is important for you, you can order the array

MySql Recursive - get all children and parents from a given id

I would use two separate recursive queries: one to bring the children, the other for the parents, and then union the results. You can keep track of the level of each node to properly order the records int he resultset:

with recursive 
    children as (
        select 1 as lvl, d.* from department d where id = 6
        union all
        select c.lvl, d.* from department d inner join children c on c.id = d.father
    ),
    parents as (
        select 1 as lvl, d.* from department d where id = 6
        union all
        select p.lvl - 1, d.* from department d inner join parents p on d.id = p.father
    )
select * from parents
union   -- on purpose, to remove the duplicate on id 6
select * from children
order by lvl;

This is safer than having multiple union all members in the same query. MySQL does not guarantee the order of evaluation of the members in the recursion, so using this technique could lead to unexpected behavior.

Demo on DB Fiddle

Unrelated to your question, but: the following can be seen in your code:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET SESSION sql_mode = '';

Just don't. ONLY_FULL_GROUP_BY is there for a good reason, that is to have MySQL behave consistenly with the SQL standard as regard to aggregation query. Disabling this SQL mode is never a good idea.

Recursive query to retrieve all child ids below

Edit: As you commented below, it turned out your problem on the level was what I could not understand.

As you have probably understood, a recursive CTE is divided into 2 parts.

WITH RECURSIVE MyCTE AS (
    <Start of the recursion/loop>
    UNION
    <Step from level N to level N+1/N-1>
)

We will:

1. Change the starting point of the recursion to start from the correct manager, not the guy on top of the hierarchy.
2. Browse into the hierarchy the same way you did
3. Change the loop so that a branch is extended back to the top boss, for the purpose of calculating the level correctly.

Postgresql only allows 1 recursive CTE (not sure for other DBMS), so we will need to do 2 and 3 together. We only need a little extra caution to allow the query to start with several starting point (id_manager IN (...))

WITH RECURSIVE tree(id_root_manager, id_direct_manager, id_employee, level) AS (
    SELECT id_manager,
           id_manager,
           id_employee,
           UNNEST(ARRAY[0,1]) /* level > 0: go to the bottom, level <= 0: go to the top of the hierarchy */
    FROM   people
    WHERE  id_manager IN ('00555')
    UNION ALL
    SELECT id_root_manager,
           id_manager,
           p.id_employee,
           CASE WHEN level <= 0 THEN level-1 ELSE level+1 END
    FROM   people p
    JOIN    tree   t ON (level > 0 AND p.id_manager = t.id_employee) OR (level <= 0 AND t.id_direct_manager = p.id_employee)
)
SELECT id_root_manager, id_direct_manager, id_employee, level - (SELECT MIN(level) FROM tree WHERE id_root_manager = h.id_root_manager) AS level
FROM tree h
WHERE level > 0
ORDER BY id_root_manager, level

If you are not interested in the "root" manager, you may want to avoid duplicates by changing the final select to:

SELECT DISTINCT id_direct_manager, id_employee, level - (SELECT MIN(level) FROM tree WHERE id_root_manager = h.id_root_manager) AS level
FROM tree h
WHERE level > 0
ORDER BY level

recursive query to get all children

Schema (PostgreSQL v12)

-- create the table
create table edibles (
  id integer not null,
  name varchar(60) not null,
  parent integer not null);
  
-- insert data
insert into edibles (id, name, parent) values
  (1, 'fruit', 0),
  (2, 'veg', 0),
  (3, 'cruciferous', 2),
  (4, 'horseradish', 3),
  (5, 'colanaceae', 1),
  (6, 'tomatoes', 5),
  (7, 'aubergine', 5),
  (8, 'chinese eggplant', 7),
  (9, 'costoluto fiorentino', 6),
  (10, 'calvaceae', 0);

Query #1

with RECURSIVE cte as 
(
  select * from edibles where id=1
  union all
  select e.* from edibles e inner join cte on e.parent=cte.id
)
select * from cte;

with recursive subcategories as (
    select id, "name", "parentCategoryId", 0 as level
    from category c2 
    where id = 85
    union
        select c3.id, c3."name", c3."parentCategoryId", subs.level + 1
        from category c3
        inner join subcategories subs on subs."id" = c3."parentCategoryId"
        where subs.level < YOUR_LIMIT
) select * from subcategories

public function getChildrenIds($folders, $parent_id)  
{
    // Prime array with parent looking for
    $folderIds = [$parent_id];
    foreach($folders as $folder) {
        // Check if matches any parent so far found
        if( in_array($folder['parent'], $folderIds) ) {
            $folderIds[] = $folder['id'];
        }
    }

    // Remove parent id added at start
    array_shift($folderIds);
    return $folderIds;
}

function iterChildren(cat) {
  let c = cat, children = [];

  while (c.child) {
    children.push({id: c.child.id});
    c = c.child;
  }

  return {id: cat.id, children: children};
}

let newData = data.map(iterChildren);

function getChildren(array, id) {

    return array.reduce((r, { menuId, parentId }) => {

        if (parentId === id) {

            r.push(menuId, ...getChildren(array, menuId));

        }

        return r;

    }, []);

}

var data = [{ menuId: "1001", depth: "1", parentId: "0" }, { menuId: "1002", depth: "1", parentId: "0" }, { menuId: "1003", depth: "2", parentId: "1001" }, { menuId: "1004", depth: "2", parentId: "1001" }, { menuId: "1005", depth: "3", parentId: "1003" }, { menuId: "1006", depth: "3", parentId: "1004" }, { menuId: "1007", depth: "4", parentId: "1006" }, { menuId: "1008", depth: "4", parentId: "1006" }, { menuId: "1009", depth: "5", parentId: "1008" }],

    result = getChildren(data, '1004');

console.log(result);