Querying Hierarchical Data with Recursive CTEs

WITH RECURSIVE CategoryPath AS (
    -- Anchor member: Select all root categories (those with no parent)
    SELECT
        id,
        name,
        parent_id,
        CAST(name AS VARCHAR(MAX)) AS full_path, -- Adjust VARCHAR size based on expected path length
        0 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive member: Join categories with their children to build paths
    SELECT
        c.id,
        c.name,
        c.parent_id,
        CAST(cp.full_path + ' -> ' + c.name AS VARCHAR(MAX)), -- SQL Server. Use 'cp.full_path || ' -> ' || c.name' for PostgreSQL/Oracle/SQLite or CONCAT for MySQL
        cp.level + 1
    FROM categories c
    INNER JOIN CategoryPath cp ON c.parent_id = cp.id
)
SELECT id, name, parent_id, full_path, level
FROM CategoryPath
ORDER BY full_path;

-- Example 'categories' table structure:
-- CREATE TABLE categories (
--     id INT PRIMARY KEY,
--     name VARCHAR(255),
--     parent_id INT REFERENCES categories(id)
-- );
-- INSERT INTO categories (id, name, parent_id) VALUES
-- (1, 'Electronics', NULL),
-- (2, 'Smartphones', 1),
-- (3, 'Laptops', 1),
-- (4, 'Accessories', 2),
-- (5, 'Chargers', 4),
-- (6, 'Cases', 4),
-- (7, 'Dell', 3),
-- (8, 'Apple', 3),
-- (9, 'Fashion', NULL),
-- (10, 'Men''s', 9),
-- (11, 'Women''s', 9);