Реализация категорий каталога на PHP: выбор оптимальной структуры

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

Пример 1: Построение дерева категорий с уровнем вложенности

Используя closure table, построим ассоциативный массив, группируя по родителю:

function buildTree(PDO $pdo): array {
    $stmt = $pdo->query('
        SELECT c.id, c.name, c.slug, cc.ancestor_id, cc.depth
        FROM categories c
        JOIN categories_closure cc ON cc.descendant_id = c.id
        WHERE cc.depth = 1 OR cc.depth = 0
        ORDER BY cc.ancestor_id, cc.depth, c.name
    ');
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $tree = [];
    $map = [];
    
    foreach ($rows as $row) {
        $map[$row['id']] = ['id' => $row['id'], 'name' => $row['name'], 'children' => []];
        if ($row['depth'] == 0) {
            $tree[$row['id']] = &$map[$row['id']];
        } else {
            $parentId = $row['ancestor_id'];
            if (isset($map[$parentId])) {
                $map[$parentId]['children'][] = &$map[$row['id']];
            }
        }
    }
    return $tree;
}

Результат:

Array
(
    [1] => Array
        (
            [id] => 1
            [name] => Электроника
            [children] => Array
                (
                    [0] => Array
                        (
                            [id] => 2
                            [name] => Телефоны
                            [children] => Array()
                        )
                )
        )
)

Пример 2: Миграция с adjacency list на closure table

Перенос данных из таблицы с parent_id в структуру closure:

// Предположим, таблица source: id, parent_id (NULL для корня)
function migrateToClosure(PDO $pdo): void {
    $pdo->beginTransaction();
    try {
        // 1. Копируем все категории (без изменения)
        $pdo->exec('INSERT INTO categories (id, name, slug) SELECT id, name, slug FROM source');
        
        // 2. Добавляем самоссылки depth=0
        $pdo->exec('INSERT INTO categories_closure (ancestor_id, descendant_id, depth) 
                     SELECT id, id, 0 FROM categories');
        
        // 3. Для каждой записи source с parent_id добавляем пути от предков родителя
        // В цикле (можно рекурсивным CTE, но проще итеративно)
        $parents = $pdo->query('SELECT id, parent_id FROM source WHERE parent_id IS NOT NULL')->fetchAll();
        $processed = [];
        while (!empty($parents)) {
            $newPaths = [];
            foreach ($parents as $row) {
                $childId = $row['id'];
                $parentId = $row['parent_id'];
                // Получаем все пути от предков родителя
                $stmt = $pdo->prepare('SELECT ancestor_id, depth FROM categories_closure WHERE descendant_id = ?');
                $stmt->execute([$parentId]);
                $ancestors = $stmt->fetchAll();
                foreach ($ancestors as $anc) {
                    $newPaths[] = [
                        'ancestor_id' => $anc['ancestor_id'],
                        'descendant_id' => $childId,
                        'depth' => $anc['depth'] + 1
                    ];
                }
                $processed[] = $childId;
            }
            // Вставка всех накопленных путей
            $stmt = $pdo->prepare('INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (?, ?, ?)');
            foreach ($newPaths as $path) {
                $stmt->execute($path);
            }
            // Следующая итерация - находим детей, которые ещё не обработаны
            $placeholders = implode(',', array_fill(0, count($processed), '?'));
            $parents = $pdo->prepare("SELECT id, parent_id FROM source WHERE parent_id IN ($placeholders) AND id NOT IN ($placeholders)");
            $parents->execute(array_merge($processed, $processed));
            $parents = $parents->fetchAll();
        }
        $pdo->commit();
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

Этот код обрабатывает дерево уровня за уровнем, избегая рекурсии. На практике для больших деревьев лучше использовать рекурсивное CTE внутри БД.

Пример 3: Bulk-операции с closure table (добавление поддерева)

Если требуется добавить сразу несколько категорий как потомков существующей, можно вставить все новые категории, получить их ID, затем одним запросом вставить замыкания:

// $newNodes = [['name'=>'A', 'slug'=>'a'], ['name'=>'B', 'slug'=>'b']];
$parentId = 5;
$pdo->beginTransaction();
try {
    $stmt = $pdo->prepare('INSERT INTO categories (name, slug) VALUES (?, ?)');
    $ids = [];
    foreach ($newNodes as $node) {
        $stmt->execute([$node['name'], $node['slug']]);
        $ids[] = (int)$pdo->lastInsertId();
    }
    // Вставляем самоссылки
    $pdo->prepare('INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (?, ?, 0)')
       ->execute(array_map(function($id) { return [$id, $id]; }, $ids));
    
    // Копируем пути от предков родителя для каждого нового ID
    $stmt = $pdo->prepare('
        INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
        SELECT ancestor_id, ?, depth + 1
        FROM categories_closure
        WHERE descendant_id = ?
    ');
    foreach ($ids as $id) {
        $stmt->execute([$id, $parentId]);
    }
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
}

Пример 4: Получение хлебных крошек с помощью nested sets (альтернативный вариант)

В nested sets для получения цепочки предков можно использовать условие между lft и rgt:

-- Получить все категории, предков для категории с id=42
SELECT parent.id, parent.name
FROM categories_ns AS node
JOIN categories_ns AS parent 
   ON parent.lft <= node.lft AND parent.rgt >= node.rgt
WHERE node.id = 42
ORDER BY parent.lft;

Результат:

+------+--------------+
| id   | name         |
+------+--------------+
| 1    | Корень       |
| 5    | Электроника  |
| 12   | Телефоны     |
| 42   | Смартфоны    |
+------+--------------+

Пример 5: Проблема избыточности closure table и её решение

При большом количестве категорий (сотни тысяч) таблица closure может занимать гигабайты. Один из способов уменьшить объём - хранить только пути до определённой глубины (например, до 4), а более глубокие уровни получать рекурсивно через adjacency list.

-- Гибридная схема: closure только для корня и первого уровня
CREATE TABLE categories_hybrid (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT
);

CREATE TABLE categories_closure_limited (
    ancestor_id INT,
    descendant_id INT,
    depth TINYINT,
    PRIMARY KEY (ancestor_id, descendant_id)
);
-- Заполняем только пути depth <= 3

Это компромисс, используемый в некоторых CMS, когда требуется баланс между производительностью выборки и размером данных.