Articles in cat tree, w/path

Fork of Articles w/title matching regex in cat tree, w/path by Cryptic

This query is marked as a draft This query has been published by HTinC23.

SQL

SET @basecat='Dutch_Protestants';
SET max_recursive_iterations=3;

WITH RECURSIVE deepcat (subcat, catpath, depth) AS
(
  SELECT @basecat, @basecat, 0

  UNION

  SELECT page_title, CONCAT(catpath, ' > ', page_title), depth + 1
  FROM categorylinks
  JOIN page ON page_id = cl_from AND page_namespace = 14
  JOIN deepcat ON cl_to = subcat
)
SELECT DISTINCT CONCAT("*[[", if(page_namespace = 0, page_title, concat("Category:", page_title)), "]]"),
       #MIN(depth),
       MIN(catpath) OVER (PARTITION BY page_title ORDER BY depth ASC) AS 'category path'
FROM page
JOIN categorylinks ON cl_from = page_id
JOIN deepcat ON cl_to = subcat
WHERE page_namespace in (0,14)
  AND page_is_redirect = 0
GROUP BY page_title
ORDER BY MIN(depth) ASC;

SQL

Checking query status...