Traverse Hierarchical Data with Recursive CTE (PostgreSQL)

-- Sample table for hierarchical categories
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id INT REFERENCES categories(id)
);

-- Insert sample data (a tree structure)
INSERT INTO categories (id, name, parent_id) VALUES
(1, 'Electronics', NULL),
(2, 'Computers', 1),
(3, 'Smartphones', 1),
(4, 'Laptops', 2),
(5, 'Desktops', 2),
(6, 'Gaming Laptops', 4),
(7, 'Accessories', 1);

-- Find all descendants of 'Electronics' (id = 1)
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE id = 1 -- Starting category (Electronics)

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, name, parent_id, depth FROM category_tree ORDER BY depth, id;

-- Find all ancestors of 'Gaming Laptops' (id = 6)
WITH RECURSIVE category_ancestors AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE id = 6 -- Starting category (Gaming Laptops)

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ca.depth + 1
    FROM categories c
    JOIN category_ancestors ca ON c.id = ca.parent_id
)
SELECT id, name, parent_id, depth FROM category_ancestors ORDER BY depth DESC, id;