Работа с категориями товаров в PHP: от таблиц до иерархий

Расширенные примеры работы с категориями

Пример 1. Полный CRUD для категорий с PDO (adjacency list)

Класс для управления категориями с проверками и транзакциями.

class CategoryManager {
    private PDO $pdo;

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

    // Создание категории
    public function create(string $name, ?int $parentId = null): int {
        if ($parentId !== null) {
            $this->ensureCategoryExists($parentId);
        }
        $stmt = $this->pdo->prepare('INSERT INTO categories (name, parent_id) VALUES (?, ?)');
        $stmt->execute([$name, $parentId]);
        return (int)$this->pdo->lastInsertId();
    }

    // Обновление категории
    public function update(int $id, string $name, ?int $parentId = null): void {
        $this->ensureCategoryExists($id);
        if ($parentId !== null) {
            if ($this->isSelfOrDescendant($id, $parentId)) {
                throw new Exception('Нельзя назначить дочернюю категорию родителем');
            }
        }
        $stmt = $this->pdo->prepare('UPDATE categories SET name=?, parent_id=? WHERE id=?');
        $stmt->execute([$name, $parentId, $id]);
    }

    // Удаление категории (с переносом дочерних на уровень выше)
    public function delete(int $id): void {
        $this->pdo->beginTransaction();
        try {
            // Переназначаем parent_id у дочерних категорий
            $parent = $this->getParentId($id);
            $stmt = $this->pdo->prepare('UPDATE categories SET parent_id=? WHERE parent_id=?');
            $stmt->execute([$parent, $id]);
            // Удаляем саму категорию
            $stmt = $this->pdo->prepare('DELETE FROM categories WHERE id=?');
            $stmt->execute([$id]);
            $this->pdo->commit();
        } catch (Exception $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

    // Вспомогательные методы
    private function getParentId(int $id): ?int {
        $stmt = $this->pdo->prepare('SELECT parent_id FROM categories WHERE id=?');
        $stmt->execute([$id]);
        return $stmt->fetchColumn() ?: null;
    }

    private function isSelfOrDescendant(int $id, int $potentialParent): bool {
        // Проверка с помощью рекурсивного CTE
        $sql = 'WITH RECURSIVE descendants AS (
                    SELECT id FROM categories WHERE parent_id = ?
                    UNION ALL
                    SELECT c.id FROM categories c
                    INNER JOIN descendants d ON c.parent_id = d.id
                )
                SELECT 1 FROM descendants WHERE id = ?';
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$id, $potentialParent]);
        return (bool)$stmt->fetchColumn();
    }

    private function ensureCategoryExists(int $id): void {
        $stmt = $this->pdo->prepare('SELECT 1 FROM categories WHERE id=?');
        $stmt->execute([$id]);
        if (!$stmt->fetchColumn()) {
            throw new Exception('Категория не найдена');
        }
    }
}

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

$manager = new CategoryManager($pdo);
$rootId = $manager->create('Корневая');
$childId = $manager->create('Дочерняя', $rootId);
$manager->update($childId, 'Изменённая дочерняя', null);  // переместить в корень
$manager->delete($rootId);  // дочерние станут корневыми

Пример 2. Выборка дерева категорий в один запрос с рекурсивным CTE

Генерация вложенного массива для построения меню.

function getCategoryTree(PDO $pdo): array {
    $sql = 'WITH RECURSIVE tree AS (
        SELECT id, name, parent_id, 0 AS level
        FROM categories
        WHERE parent_id IS NULL
        UNION ALL
        SELECT c.id, c.name, c.parent_id, t.level + 1
        FROM categories c
        JOIN tree t ON c.parent_id = t.id
    )
    SELECT * FROM tree ORDER BY level, name';
    $stmt = $pdo->query($sql);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Преобразуем в иерархический массив
    $tree = [];
    $index = [];
    foreach ($rows as $row) {
        $node = ['id' => $row['id'], 'name' => $row['name'], 'children' => []];
        $index[$row['id']] = &$node;
        if ($row['parent_id'] === null) {
            $tree[] = &$node;
        } else {
            $index[$row['parent_id']]['children'][] = &$node;
        }
        unset($node);
    }
    return $tree;
}

Пример вывода:

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

Пример 3. Использование Closure Table для получения всех предков категории

function getAncestors(int $categoryId, PDO $pdo): array {
    $sql = 'SELECT a.id, a.name, cc.depth
            FROM categories_closure cc
            JOIN categories a ON a.id = cc.ancestor_id
            WHERE cc.descendant_id = ?
            ORDER BY cc.depth DESC';
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$categoryId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Пример для категории id=12:
// Массив: [ ['id'=>1,'name'=>'Корень','depth'=>3], ['id'=>5,'name'=>'Подкатегория','depth'=>2], ['id'=>12,'name'=>'Текущая','depth'=>0] ]

Результат: формирование хлебных крошек (breadcrumbs).

Пример 4. Модификация Nested Sets: перемещение поддерева

-- Перемещение категории с id=10 (включая потомков) под родителя id=20
-- В реальной реализации лучше использовать библиотеку, но принцип:
-- 1. Определяем размер поддерева: @size = (rgt - lft + 1)
-- 2. Вставляем временный промежуток под новым родителем
-- 3. Сдвигаем lft/rgt поддерева на новое место
-- 4. Удаляем старый промежуток

Пример кода на PHP (упрощённый):

function moveSubtree(int $nodeId, int $newParentId, PDO $pdo): void {
    $pdo->beginTransaction();
    try {
        // Получаем lft, rgt перемещаемого узла
        $stmt = $pdo->prepare('SELECT lft, rgt FROM categories_ns WHERE id=?');
        $stmt->execute([$nodeId]);
        $node = $stmt->fetch(PDO::FETCH_ASSOC);
        $size = $node['rgt'] - $node['lft'] + 1;

        // Получаем rgt нового родителя
        $stmt = $pdo->prepare('SELECT rgt FROM categories_ns WHERE id=?');
        $stmt->execute([$newParentId]);
        $parentRgt = $stmt->fetchColumn();

        // Освобождаем место под новые узлы (сдвиг вправо)
        $pdo->exec("UPDATE categories_ns SET lft = lft + {$size} WHERE lft > {$parentRgt}");
        $pdo->exec("UPDATE categories_ns SET rgt = rgt + {$size} WHERE rgt >= {$parentRgt}");

        // Сдвигаем само поддерево на новое место
        $diff = $parentRgt - $node['lft'] + 1;
        $pdo->exec("UPDATE categories_ns SET lft = lft + {$diff}, rgt = rgt + {$diff} WHERE lft BETWEEN {$node['lft']} AND {$node['rgt']}");

        // Убираем старый промежуток (сдвиг влево)
        $pdo->exec("UPDATE categories_ns SET lft = lft - {$size} WHERE lft > {$node['rgt']}");
        $pdo->exec("UPDATE categories_ns SET rgt = rgt - {$size} WHERE rgt > {$node['rgt']}");

        $pdo->commit();
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

Пример 5. Поиск продуктов через несколько категорий (Materialized Path)

-- Предположим, у товаров есть category_path, копирующий путь категории
SELECT p.name, p.price
FROM products p
WHERE p.category_path LIKE '1/5/%' OR p.category_path LIKE '1/10/%';

Или если путь товара хранит полный путь категории, можно не использовать JOIN.

+----------------+--------+
| name           | price  |
+----------------+--------+
| Ноутбук A      | 50000  |
| Планшет B      | 20000  |
+----------------+--------+