Recursive query to determine the page level and root page for a tree of pages

Recursive Query

      WITH RECURSIVE page_list AS (
        SELECT root.id, root.title, root.parent_id, root.published_at, 
          1 AS page_level,
          root.id AS root_id,
          root.title AS root_title
        FROM pages AS root
        WHERE root.parent_id IS NULL

        UNION ALL

        SELECT child.id, child.title, child.parent_id, child.published_at, 
        pl.page_level + 1,
        pl.root_id,
          pl.root_title
        FROM pages AS child
          INNER JOIN page_list AS pl ON child.parent_id = pl.id
        WHERE child.parent_id IS NOT NULL AND child.published_at IS NOT NULL
      )

      SELECT * FROM page_list ORDER BY root_title, title