Выбор типа каталога для PHP-приложения

Расширенные примеры и сценарии

Closure Table: полная реализация с функциями на PHP

Пример на PHP (PDO) для работы с Closure Table.

class CategoryManager {
    private PDO $pdo;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

    public function addCategory(string $name, ?int $parentId = null): int {
        $this->pdo->beginTransaction();
        try {
            // Вставка узла
            $stmt = $this->pdo->prepare('INSERT INTO categories (name) VALUES (?)');
            $stmt->execute([$name]);
            $newId = (int)$this->pdo->lastInsertId();

            // Вставка замыкания для самого себя
            if ($parentId === null) {
                // Корень
                $stmt = $this->pdo->prepare('INSERT INTO category_closure (ancestor_id, descendant_id, depth) VALUES (?,?,0)');
                $stmt->execute([$newId, $newId]);
            } else {
                // Копируем замыкания родителя и добавляем себя
                $stmt = $this->pdo->prepare('
                    INSERT INTO category_closure (ancestor_id, descendant_id, depth)
                    SELECT ancestor_id, ?, depth+1
                    FROM category_closure
                    WHERE descendant_id = ?
                    UNION ALL
                    SELECT ?, ?, 0
                ');
                $stmt->execute([$newId, $parentId, $newId, $newId]);
            }
            $this->pdo->commit();
            return $newId;
        } catch (Exception $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

    public function getSubtree(int $id): array {
        $stmt = $this->pdo->prepare('
            SELECT c.*, cc.depth
            FROM categories c
            JOIN category_closure cc ON c.id = cc.descendant_id
            WHERE cc.ancestor_id = ?
            ORDER BY cc.depth, c.name
        ');
        $stmt->execute([$id]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function moveSubtree(int $nodeId, ?int $newParentId): void {
        // Перемещение поддерева: удалить старые замыкания, добавить новые
        $this->pdo->beginTransaction();
        try {
            // Удаляем все связи, где данный узел или его потомки являются потомками
            $stmt = $this->pdo->prepare('
                DELETE FROM category_closure
                WHERE descendant_id IN (
                    SELECT descendant_id FROM category_closure WHERE ancestor_id = ?
                )
                AND ancestor_id IN (
                    SELECT ancestor_id FROM category_closure WHERE descendant_id = ? AND ancestor_id != ?
                )
            ');
            $stmt->execute([$nodeId, $nodeId, $nodeId]);

            // Вставляем новые замыкания от нового родителя до всех потомков узла
            $stmt = $this->pdo->prepare('
                INSERT INTO category_closure (ancestor_id, descendant_id, depth)
                SELECT super.ancestor_id, sub.descendant_id, super.depth + sub.depth + 1
                FROM category_closure AS super
                CROSS JOIN category_closure AS sub
                WHERE super.descendant_id = ?
                AND sub.ancestor_id = ?
            ');
            $stmt->execute([$newParentId, $nodeId]);
            $this->pdo->commit();
        } catch (Exception $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
}

Adjacency List: получение предков без CTE (старый MySQL)

Если MySQL < 8, можно использовать множественные запросы в цикле PHP:

function getAncestors(PDO $pdo, int $nodeId): array {
    $ancestors = [];
    $currentId = $nodeId;
    while ($currentId !== null) {
        $stmt = $pdo->prepare('SELECT id, name, parent_id FROM categories WHERE id = ?');
        $stmt->execute([$currentId]);
        $node = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$node) break;
        $ancestors[] = $node;
        $currentId = $node['parent_id'];
    }
    return array_reverse($ancestors);
}

// Пример использования:
$ancestors = getAncestors($pdo, 5);
print_r($ancestors);

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Электроника
            [parent_id] => 
        )
    [1] => Array
        (
            [id] => 3
            [name] => Компьютеры
            [parent_id] => 1
        )
    [2] => Array
        (
            [id] => 5
            [name] => Ноутбуки
            [parent_id] => 3
        )
)

Nested Sets: вставка с блокировкой и пересчётом

Пример хранимой процедуры для MySQL (осторожно, блокирует таблицу):

DELIMITER //
CREATE PROCEDURE insert_category(IN p_name VARCHAR(255), IN p_parent_lft INT)
BEGIN
    DECLARE v_right INT;
    -- Находим правый ключ родителя
    SELECT rgt INTO v_right FROM categories WHERE lft = p_parent_lft;
    -- Сдвигаем узлы
    UPDATE categories SET rgt = rgt + 2 WHERE rgt >= v_right;
    UPDATE categories SET lft = lft + 2 WHERE lft > v_right;
    -- Вставляем новую категорию
    INSERT INTO categories (name, lft, rgt) VALUES (p_name, v_right, v_right + 1);
END //
DELIMITER ;

-- Вызов: CALL insert_category('Смартфоны', 1);

Path Enumeration: обновление путей при удалении узла

Пример триггера для автоматического обновления потомков:

CREATE TRIGGER `after_delete_category`
AFTER DELETE ON `categories` FOR EACH ROW
BEGIN
    UPDATE categories
    SET path = REPLACE(path, CONCAT(OLD.path, OLD.id, '/'), '')
    WHERE path LIKE CONCAT(OLD.path, OLD.id, '/%');
END;

-- После удаления категории 'Компьютеры' (id=3, path='1/3/')
-- Путь '1/3/5/' станет '1/5/' - это может сломать иерархию. Нужно аккуратно.
-- Лучше использовать триггер BEFORE DELETE для пересчёта, либо запретить удаление нелистовых узлов.

Сравнение производительности (пример с 10 000 категорий)