Категории контента: выбор архитектуры для управления данными

Расширенные примеры для подхода Nested Sets.

Пример 1. Функция построения дерева в PHP с вложенными множествами (без рекурсии, используя сортировку по lft):

function buildTree($pdo) {
    $stmt = $pdo->query('SELECT id, name, lft, rgt FROM categories ORDER BY lft');
    $rows = $stmt->fetchAll();
    $tree = [];
    $stack = [];
    foreach ($rows as $row) {
        $row['children'] = [];
        $depth = count($stack);
        while (!empty($stack) && $stack[count($stack)-1]['rgt'] < $row['lft']) {
            array_pop($stack);
        }
        if (!empty($stack)) {
            $stack[count($stack)-1]['children'][] = &$row;
        } else {
            $tree[] = &$row;
        }
        $stack[] = &$row;
    }
    return $tree;
}
$tree = buildTree($pdo);
// Вывод в виде JSON
header('Content-Type: application/json');
echo json_encode($tree, JSON_PRETTY_PRINT);

[
  {
    "id": 1,
    "name": "Root",
    "lft": 1,
    "rgt": 20,
    "children": [
      {
        "id": 2,
        "name": "Child A",
        "lft": 2,
        "rgt": 9,
        "children": [...]
      },
      ...
    ]
  }
]

Пример 2. Перемещение узла в другое место (внутри одного уровня):

function moveNode($pdo, $nodeId, $newParentId) {
    // Получаем данные узла и нового родителя
    $stmt = $pdo->prepare('SELECT lft, rgt, (rgt - lft + 1) AS width FROM categories WHERE id = ?');
    $stmt->execute([$nodeId]);
    $node = $stmt->fetch();
    $stmt = $pdo->prepare('SELECT rgt FROM categories WHERE id = ?');
    $stmt->execute([$newParentId]);
    $parent = $stmt->fetch();
    $pdo->beginTransaction();
    // Удаляем узел из старой позиции (все значения временно отрицательны)
    $pdo->exec("UPDATE categories SET lft = -lft, rgt = -rgt WHERE lft >= {$node['lft']} AND rgt <= {$node['rgt']}");
    // Сдвигаем оставшиеся узлы
    $pdo->exec("UPDATE categories SET lft = lft - {$node['width']} WHERE lft > {$node['rgt']}");
    $pdo->exec("UPDATE categories SET rgt = rgt - {$node['width']} WHERE rgt > {$node['rgt']}");
    // Сдвигаем нового родителя и соседей, чтобы освободить место
    $pdo->exec("UPDATE categories SET lft = lft + {$node['width']} WHERE lft > {$parent['rgt']}");
    $pdo->exec("UPDATE categories SET rgt = rgt + {$node['width']} WHERE rgt >= {$parent['rgt']}");
    // Восстанавливаем узел с новыми значениями
    $newLft = $parent['rgt'] + 1;
    $pdo->exec("UPDATE categories SET lft = -lft + ($newLft - {$node['lft']}), rgt = -rgt + ($newLft - {$node['lft']}) WHERE lft < 0");
    $pdo->commit();
}

Примечание:

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

Пример 3. Удаление узла вместе с поддеревом:

function deleteSubtree($pdo, $nodeId) {
    $stmt = $pdo->prepare('SELECT lft, rgt, (rgt - lft + 1) AS width FROM categories WHERE id = ?');
    $stmt->execute([$nodeId]);
    $node = $stmt->fetch();
    $pdo->beginTransaction();
    $pdo->exec("DELETE FROM categories WHERE lft >= {$node['lft']} AND rgt <= {$node['rgt']}");
    $pdo->exec("UPDATE categories SET lft = lft - {$node['width']} WHERE lft > {$node['rgt']}");
    $pdo->exec("UPDATE categories SET rgt = rgt - {$node['width']} WHERE rgt > {$node['rgt']}");
    $pdo->commit();
}

Результат выполнения:

-- До удаления:
 id | name  | lft | rgt
  1 | Root  |  1  | 20
  2 | Child |  2  |  9
  3 | Sub   |  3  |  4
-- После удаления узла id=2:
 id | name  | lft | rgt
  1 | Root  |  1  | 12

Пример 4. Для подхода Closure Table – получение всех предков (ancestors) заданного узла:

SELECT a.name AS ancestor, cl.depth
FROM categories_closure cl
JOIN categories a ON cl.ancestor_id = a.id
WHERE cl.descendant_id = 5
ORDER BY cl.depth DESC;

+----------+-------+
| ancestor | depth |
+----------+-------+
| Root     |     2 |
| Parent   |     1 |
| Self     |     0 |
+----------+-------+