Traverse categories tree

categories-bottom-to-top.sql

WITH RECURSIVE child_to_parent_query AS (
    SELECT category.id, 
           category.parent_category_id,
           0 as level
        FROM category 
            LEFT JOIN category excludeChildren 
                ON excludeChildren.parent_category_id = category.id
        WHERE excludeChildren.id IS NULL
    UNION DISTINCT
    SELECT category.id,
          category.parent_category_id,
          child_to_parent_query.level + 1
        FROM category
    INNER JOIN child_to_parent_query
        ON child_to_parent_query.parent_category_id = category.id
)
SELECT id, parent_category_id, level
FROM child_to_parent_query

categories-top-to-bottom.sql

WITH RECURSIVE parent_to_child_query AS (
    SELECT category.id, 
           category.parent_category_id,
           0 as level
        FROM category
        WHERE category.parent_category_id IS NULL
    UNION ALL
    SELECT category.id,
          category.parent_category_id,
          parent_to_child_query.level + 1
        FROM category
    INNER JOIN parent_to_child_query
        ON parent_to_child_query.id = category.parent_category_id
)
SELECT id, parent_category_id, level
FROM parent_to_child_query

Aso, in case of bottom to top traversal, you'd likely need to remove duplicated ids.

        // exclude duplicated categories because of paths from leafs of different levels (the farthest level is used)
        $ids = array_column($resultSet, 'id');
        $result = array_combine($ids, $resultSet);

        // make sure records are ordered by their distance (level) from the farthest leafs
        $sortOrder = array_flip($ids);
        array_multisort($sortOrder, $result);