rec.sql

  WITH RECURSIVE tree AS (SELECT *, id AS root_category
                              FROM categories
                             WHERE parentId IS NULL
                             UNION ALL
                            SELECT c.*, p.root_category
                              FROM categories c
                                       JOIN tree p
                                       ON c.parentId = p.id)
  SELECT *
    FROM tree