Untitled query #87422

SQL

WITH RECURSIVE category_tree AS (
  -- Base case: direct parent categories
  SELECT 
    p1.page_id as child_id,
    p1.page_title as child_title,
    p.page_title as parent_title,
    1 as level
  FROM categorylinks cl
  JOIN page p1 ON p1.page_title = 'Београд'
    AND p1.page_namespace = 14
  JOIN page p ON cl.cl_to = p.page_title
  WHERE cl.cl_from = p1.page_id
    AND cl.cl_type = 'subcat'

  UNION ALL

  -- Recursive case: find parents of parents
  SELECT 
    ct.child_id,
    ct.child_title,
    p.page_title as parent_title,
    ct.level + 1
  FROM category_tree ct
  JOIN page p1 ON p1.page_title = ct.parent_title
    AND p1.page_namespace = 14
  JOIN categorylinks cl ON cl.cl_from = p1.page_id
  JOIN page p ON cl.cl_to = p.page_title
  WHERE cl.cl_type = 'subcat'
)
SELECT 
  child_title,
  parent_title,
  level
FROM category_tree
ORDER BY level, parent_title;
SQL

Checking query status...
