Recursive CTE

WITH RECURSIVE category_tree AS (

  SELECT id, name, parent_id, 0 AS depth

  FROM categories

  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, t.depth + 1

  FROM categories AS c

  INNER JOIN category_tree AS t

    ON c.parent_id = t.id