When working with complex data structures like nested sets in PHP applications, what are some alternative approaches to recursive database queries that could improve performance and maintainability?

When working with complex data structures like nested sets in PHP applications, one alternative approach to recursive database queries is to use a closure table. A closure table is a separate table that stores the relationships between nodes in the nested set, allowing for faster and more efficient queries without the need for recursive operations.

// Example of using a closure table to query nested sets in PHP

// Define the closure table structure
CREATE TABLE closure_table (
    ancestor_id INT NOT NULL,
    descendant_id INT NOT NULL,
    depth INT NOT NULL,
    PRIMARY KEY (ancestor_id, descendant_id)
);

// Query to get all descendants of a node
SELECT descendant_id
FROM closure_table
WHERE ancestor_id = :node_id
AND depth > 0;

// Query to get all ancestors of a node
SELECT ancestor_id
FROM closure_table
WHERE descendant_id = :node_id
AND depth > 0;