В этой статье будет показан пример того, как уменьшить количество запросов к базе данных до одного при работе с коллекциями сущностей иерархической структуры в контексте PHP и Doctrine ORM. Полный пример решения вы можете посмотреть в специальном репозитории. Статья основана на решении проблемы из обсуждения на StackOverflow.

Многие из нас сталкивались с необходимостью манипуляции иерархическими структурами данных. Например, категориями товаров. Существуют различные способы хранения подобных структур в БД, оптимизированные под те или иные варианты использования. На проекте, где работал я, использовался комбинированный способ: adjacency list + materialized path. Поэтому решение проблемы в данной статье рассчитано именно на использование adjacency list для связей между сущностями.

База данных

Для решения нашей задачи подойдет любая база данных, поддерживаемая Doctrine DBAL / ORM. Мы воспользуемся PostgreSQL. Пусть категории хранятся следующим образом:

create sequence public.category_id_seq
    increment by 1;

create table if not exists public.category
(
    id         bigint default nextval('category_id_seq'::regclass) not null primary key,
    parent_id  bigint constraint fk_category_id references public.category,
    uid        text not null,
    name       text not null,
    created_at timestamp(0) with time zone not null,
    updated_at timestamp(0) with time zone not null
);

create unique index uniq_category_uid
    on public.category (uid);

Древовидная структура категорий реализуется с помощью ссылки таблицы category самой на себя по внешнему ключу parent_id. Вместе с этим у нас есть возможность определить полный путь к категории с помощью уникального поля uid, в котором уровни вложенности будут отделяться нижним подчеркиванием. То есть, foo_bar – это категория второго уровня.

Сущность

Теперь напишем минимально необходимый код сущности:

<?php
declare(strict_types=1);

namespace Hierarchy;

use DateTimeImmutable;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping\Column;
use Doctrine\ORM\Mapping\Entity;
use Doctrine\ORM\Mapping\GeneratedValue;
use Doctrine\ORM\Mapping\Id;
use Doctrine\ORM\Mapping\JoinColumn;
use Doctrine\ORM\Mapping\ManyToOne;
use Doctrine\ORM\Mapping\OneToMany;
use Doctrine\ORM\Mapping\Table;

#[Entity, Table('category')]
class Category
{
    #[Column(name: 'parent_id', type: 'bigint', nullable: true)]
    private ?int $parentId;
    
    #[Column(name: 'uid', type: 'string', nullable: false)]
    private string $uid;

    #[OneToMany(mappedBy: 'parent', targetEntity: self::class)]
    private Collection $children;

    #[Column(name: 'created_at', type: 'datetimetz_immutable', nullable: false)]
    private DateTimeImmutable $createdAt;

    #[Column(name: 'updated_at', type: 'datetimetz_immutable', nullable: false)]
    private DateTimeImmutable $updatedAt;

    public function __construct(
        #[Id]
        #[Column(name: 'id', type: 'bigint', nullable: false)]
        #[GeneratedValue(strategy: 'NONE')]
        private int $id,
        string $uid,
        #[Column(name: 'name', type: 'string', nullable: false)]
        private string $name,
        #[ManyToOne(targetEntity: self::class, inversedBy: 'children')]
        #[JoinColumn(name: 'parent_id', referencedColumnName: 'id')]
        private ?self $parent = null
    ) {
        $this->parentId = $this->parent?->id;
        $this->uid = $this->parent === null ? $uid : sprintf('%s_%s', $this->parent->uid, $uid);
        $this->createdAt = new DateTimeImmutable();
        $this->updatedAt = new DateTimeImmutable();
        $this->children = new ArrayCollection();
    }

    public function id(): int
    {
        return $this->id;
    }

    public function uid(): string
    {
        return $this->uid;
    }

    public function name(): string
    {
        return $this->name;
    }

    public function parent(): ?self
    {
        return $this->parent;
    }

    public function parentId(): ?int
    {
        return $this->parentId;
    }
    
    public function children(): Collection
    {
        return new ArrayCollection($this->children->toArray());
    }
}

Обычно связь ManyToOne определяют одним полем. Например, parent из нашего примера. По умолчанию, при первом обращении к такому полю Doctrine сделает дополнительный запрос в БД, чтобы загрузить связанную сущность в память. Если же указана жадная загрузка fetch: EAGER, то связанная сущность будет подгружена вместе с основной сущностью. В нашем случае это привело бы к каскадной подгрузке поддерева, поскольку сущность Category ссылается сама на себя. С моей точки зрения, данный побочный эффект является крайне нежелательным из-за расхода большого количества оперативной памяти на данные, которые могут даже не понадобиться.

Поэтому, когда нам необходим только идентификатор связанной сущности (в данном случае – родительской категории), а не вся сущность целиком, мы будем использовать отдельное поле parentId. Установив значение этого поля в конструкторе, в дальнейшем мы всегда будем иметь актуальное значение идентификатора. Затем, при многократном использовании этого поля, дополнительных запросов в БД инициировано не будет. Данный подход нам пригодится далее при разработке оптимизированной подгрузки коллекции сущностей.

Репозиторий

Для начала определим интерфейс репозитория категорий. Этот интерфейс мы будем использовать при подмене реализации загрузки коллекции сущностей при тестировании.

<?php
declare(strict_types=1);

namespace Hierarchy;

interface CategoryRepositoryInterface
{
    /**
     * Возвращает новый идентификатор категории.
     *
     * Стратегия формирования идентификатора зависит от конкретной реализации.
     */
    public function nextCategoryId(): int;

    /**
     * Возвращает потомки всех уровней для категории с указанным UID.
     *
     * @return Category[]
     */
    public function findDescendantsByUid(string $uid): array;
}

Этих двух методов нам будет достаточно для тестирования. Стандартная реализация интерфейса может выглядеть следующим образом:

<?php
declare(strict_types=1);

namespace Hierarchy;

use Doctrine\ORM\EntityManagerInterface;

final readonly class DefaultCategoryRepository implements CategoryRepositoryInterface
{
    public function __construct(private EntityManagerInterface $em)
    {
    }

    public function nextCategoryId(): int
    {
        return (int) $this->em
            ->getConnection()
            ->executeQuery("select nextval('category_id_seq')")
            ->fetchOne();
    }

    /**
     * @return Category[]
     */
    public function findDescendantsByUid(string $uid): array
    {
        return $this->em
            ->createQueryBuilder()
            ->select('c')
            ->from(Category::class, 'c')
            ->where('c.uid LIKE :uid')
            ->setParameter('uid', sprintf('%s_%%', $uid))
            ->orderBy('c.uid')
            ->getQuery()
            ->getResult();
    }
}

Кратко о реализации. Метод nextCategoryId возвращает следующее значение последовательности PostgreSQL, которую мы определили в самом начале. Этим методом мы будем пользоваться для передачи идентификатора в конструктор сущностей Category и их сохранении в базу данных. Метод findDescendantsByUid возвращает массив потомков всех уровней для категории с указанным UID.

Тестирование

Чтобы проверить количество запросов в БД до и после “фикса”, воспользуемся библиотекой PHPUnit со следующей конфигурацией phpunit.xml:

<?xml version="1.0" encoding="UTF-8"?>

<!-- https://phpunit.readthedocs.io/en/latest/configuration.html -->
<phpunit xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="vendor/phpunit/phpunit/phpunit.xsd"
         colors="true"
         bootstrap="vendor/autoload.php"
>
    <php>
        <ini name="display_errors" value="1" />
        <ini name="error_reporting" value="-1" />
        <server name="DB_HOST" value="postgres" />
        <server name="DB_PORT" value="5432" />
        <server name="DB_USER" value="user" />
        <server name="DB_PASSWORD" value="user" />
        <server name="DB_NAME" value="hierarchy_article_test" />
        <server name="DB_DRIVER" value="pdo_pgsql" />
        <server name="DOCTRINE_SRC_DIR" value="./src" />
        <server name="DOCTRINE_PROXY_DIR" value="./proxy" />
        <server name="DOCTRINE_PROXY_NAMESPACE" value="HierarchyProxy\\" />
    </php>

    <testsuites>
        <testsuite name="Project Test Suite">
            <directory>tests</directory>
        </testsuite>
    </testsuites>
</phpunit>

Итак, приступим к классу тестов и создадим метод testBehavior, в котором позже будет описана логика тестирования.

<?php
declare(strict_types=1);

use PHPUnit\Framework\TestCase;

final class CategoryRepositoryTest extends TestCase
{
    public function testBehavior(): void
    {
        // здесь будет написан тест
    }
}

А пока займемся подготовкой тестовых данных. Сначала напишем метод, который будет наполнять базу данных небольшим деревом категорий.

private static function createCategories(
    EntityManagerInterface $em,
    CategoryRepositoryInterface $repository
): void {
    $parent = new Category($repository->nextCategoryId(), 'foo', 'Foo');
    $child = new Category($repository->nextCategoryId(), 'bar', 'Bar', $parent);
    $grandChild = new Category($repository->nextCategoryId(), 'qux', 'Qux', $child);
    $grandGrandChild = new Category($repository->nextCategoryId(), 'doo', 'Doo', $grandChild);

    foreach ([$parent, $child, $grandChild, $grandGrandChild] as $entity) {
        $em->persist($entity);
    }

    $em->flush();
    $em->clear();
}

Теперь нам нужны менеджер сущностей и репозиторий категорий. Опишем подключение к БД и непосредственно создание менеджера.

private static function createEntityManager(Connection $connection): EntityManagerInterface
{
    $config = new ORMConfiguration();
    $config->setMetadataDriverImpl(new AttributeDriver([$_SERVER['DOCTRINE_SRC_DIR']]));
    $config->setProxyDir($_SERVER['DOCTRINE_PROXY_DIR']);
    $config->setProxyNamespace($_SERVER['DOCTRINE_PROXY_NAMESPACE']);

    return new EntityManager($connection, $config);
}

private static function createConnection(LoggerInterface $logger): Connection
{
    $params = [
        'host' => $_SERVER['DB_HOST'],
        'port' => $_SERVER['DB_PORT'],
        'user' => $_SERVER['DB_USER'],
        'password' => $_SERVER['DB_PASSWORD'],
        'dbname' => $_SERVER['DB_NAME'],
        'driver' => $_SERVER['DB_DRIVER']
    ];

    $config = new DBALConfiguration();
    $config->setMiddlewares([
        new \Doctrine\DBAL\Logging\Middleware($logger)
    ]);

    return DriverManager::getConnection($params, $config);
}

Суть теста заключается в проверке SQL-запросов, которые инициирует Doctrine, поэтому нам нужен логгер. Надеюсь, что вы вовремя обновляете зависимости на проекте, но всё же обращу ваше внимание на конфигурацию подключения к БД. В Doctrine DBAL 3.2.0 появился механизм middleware, а метод Configuration::setSQLLogger() и класс Doctrine\DBAL\Logging\DebugStack были объявлены устаревшими и начали генерировать предупреждения. То есть, раньше можно было написать примерно следующее:

$debugStack = new DebugStack();
$em->getConnection()->getConfiguration()->setSQLLogger($debugStack);
// далее инициируем и исследуем запросы
$debugStack->queries;

Однако теперь следует использовать middleware, как в примере выше. Причем аналога DebugStack в новых версиях Doctrine DBAL нет, поэтому его придется реализовывать самостоятельно. Но это не так уж и сложно. Вот пример такого логгера:

<?php
declare(strict_types=1);

namespace Hierarchy\Tests;

use Psr\Log\AbstractLogger;
use Stringable;

final class TestDatabaseLogger extends AbstractLogger
{
    private array $queries = [];
    
    public function log($level, Stringable|string $message, array $context = []): void
    {
        if (array_key_exists('sql', $context)) {
            $this->queries[] = $context;
        }
    }

    /**
     * @return string[]
     */
    public function selectQueries(): array
    {
        $filter = static fn (string $query) => str_starts_with($query, 'SELECT');
        
        return array_values(array_filter(array_column($this->queries, 'sql'), $filter));
    }
}

Теперь соберем всё воедино в методе setUpTestData():

#[ArrayShape([TestDatabaseLogger::class, CategoryRepositoryInterface::class])]
private static function setUpTestData(string $repositoryClassName): array
{
    $logger = new TestDatabaseLogger();

    $connection = self::createConnection($logger);
    $connection->executeStatement('delete from category');

    $em = self::createEntityManager($connection);
    $repository = new $repositoryClassName($em);

    self::createCategories($em, $repository);

    return [$logger, $repository];
}

Благодаря аргументу $repositoryClassName нам не придётся “хардкодить” конкретный класс репозитория, и мы сможем протестировать обе реализации выборки, “обычную” и оптимизированную, не меняя при ни тест, ни подготовительный код. Теперь напишем сам тест:

/**
 * @dataProvider behaviourDataProvider
 */
public function testBehavior(string $repositoryClassName, array $expectedQueries): void
{
    [$logger, $repository] = self::setUpTestData($repositoryClassName);

    // foo_bar, foo_bar_qux, foo_bar_qux_doo
    $descendants = $repository->findDescendantsByUid('foo');
    
    self::assertEquals('foo_bar_qux_doo', $descendants[0]->children()->first()->children()->first()->uid());
    self::assertEquals($expectedQueries, $logger->selectQueries());
}

public function behaviourDataProvider(): array
{
    return [
        [
            DefaultCategoryRepository::class,
            [
                'SELECT c0_.parent_id AS parent_id_0, c0_.uid AS uid_1, c0_.created_at AS created_at_2, c0_.updated_at AS updated_at_3, c0_.id AS id_4, c0_.name AS name_5, c0_.parent_id AS parent_id_6 FROM category c0_ WHERE c0_.uid LIKE ? ORDER BY c0_.uid ASC',
                'SELECT t0.parent_id AS parent_id_1, t0.uid AS uid_2, t0.created_at AS created_at_3, t0.updated_at AS updated_at_4, t0.id AS id_5, t0.name AS name_6, t0.parent_id AS parent_id_7 FROM category t0 WHERE t0.parent_id = ?',
                'SELECT t0.parent_id AS parent_id_1, t0.uid AS uid_2, t0.created_at AS created_at_3, t0.updated_at AS updated_at_4, t0.id AS id_5, t0.name AS name_6, t0.parent_id AS parent_id_7 FROM category t0 WHERE t0.parent_id = ?'
            ]
        ]
    ];
}

И убедимся, что он проходит успешно:

PHPUnit 9.5.27 by Sebastian Bergmann and contributors.

.                    1 / 1 (100%)

Time: 00:00.093, Memory: 10.00 MB

OK (1 test, 2 assertions)
Process finished with exit code 0

Суть теста заключается в следующем:

1. Загрузить всех потомков сущности foo

2. Взять сущность foo_bar, несколько раз переместиться вглубь дерева с помощью вызова метода children() и убедиться, что каждый такой вызов инициирует дополнительный SQL-запрос в БД

Получается, что Doctrine загружает OneToMany-коллекцию из БД всякий раз, когда происходит обращение к соответствующему полю. Даже не смотря на то, что эти сущности уже были загружены в память. Как раз это нам и предстоит исправить.

Оптимизация

Создадим новый репозиторий ImprovedCategoryRepository со следующим содержимым:

<?php
declare(strict_types=1);

namespace Hierarchy;

use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\PersistentCollection;

final readonly class ImprovedCategoryRepository implements CategoryRepositoryInterface
{
    public function __construct(private EntityManagerInterface $em)
    {
    }

    public function nextCategoryId(): int
    {
        return (int) $this->em
            ->getConnection()
            ->executeQuery("select nextval('category_id_seq')")
            ->fetchOne();
    }

    /**
     * @return Category[]
     */
    public function findDescendantsByUid(string $uid): array
    {
        $result = $this->em
            ->createQueryBuilder()
            ->select('c')
            ->from(Category::class, 'c')
            ->indexBy('c', 'c.id')
            ->where('c.uid LIKE :uid')
            ->setParameter('uid', sprintf('%s_%%', $uid))
            ->orderBy('c.uid')
            ->getQuery()
            ->getResult();

        return $this->setUpCategoryRelations($result);
    }

    /**
     * @param Category[] $categories
     * @return Category[]
     */
    private function setUpCategoryRelations(array $categories): array
    {
        $metadata = $this->em->getClassMetadata(Category::class);
        $idField = $metadata->reflFields['id'];
        $parentField = $metadata->reflFields['parent'];
        $parentIdField = $metadata->reflFields['parentId'];
        $childrenField = $metadata->reflFields['children'];

        foreach ($categories as $category) {
            /** @var PersistentCollection $children */
            $children = $childrenField->getValue($category);
            $children->setInitialized(true);

            $parent = $categories[$parentIdField->getValue($category)] ?? null;

            if ($parent === null) {
                continue;
            }

            /** @var PersistentCollection $children */
            $children = $childrenField->getValue($parent);

            if (!$children->contains($category)) {
                $parentField->setValue($category, $parent);
                $parentIdField->setValue($category, $idField->getValue($parent));
                $children->add($category);
            }
        }

        return array_values($categories);
    }
}

DQL выборки отличается лишь тем, что мы указываем идентификатор сущности в качестве индекса массива. Это понадобится далее при обходе категорий:

$this->em
     ->createQueryBuilder()
     ->select('c')
     ->from(Category::class, 'c')
     ->indexBy('c', 'c.id')

Всё самое интересное происходит далее в методе setUpCategoryRelations(). Для начала через метаданные мы выбираем поля сущности Category, с которыми будем работать.

$metadata = $this->em->getClassMetadata(Category::class);
$idField = $metadata->reflFields['id'];
$parentField = $metadata->reflFields['parent'];
$parentIdField = $metadata->reflFields['parentId'];
$childrenField = $metadata->reflFields['children'];

Теперь мы “обманем” Doctrine и укажем, что все коллекции дочерних сущностей уже были проинициализированы:

/** @var PersistentCollection $children */
$children = $childrenField->getValue($category);
$children->setInitialized(true);

Тогда при последующих обращениях к свойству Category::$children Doctrine не будет инициировать дополнительный запрос в БД! Но это только часть нашего “фикса”, ведь коллекции остаются пустыми. Поэтому соберем их вручную:

$parent = $categories[$parentIdField->getValue($category)] ?? null;

if ($parent === null) {
    continue;
}

/** @var PersistentCollection $children */
$children = $childrenField->getValue($parent);

if (!$children->contains($category)) {
    $parentField->setValue($category, $parent);
    $parentIdField->setValue($category, $idField->getValue($parent));
    $children->add($category);

Из-за рефлексии код выглядит страшновато, но при этом имеет преимущества:

1. Технические манипуляции над сущностями отделены от бизнес-логики

2. Какое бы то ни было изменение методов сущности не затронет репозиторий и не сломает его

По факту код выше делает то же самое, как если бы в сущности мы написали такой метод и затем использовали его в репозитории:

public function addChild(self $child): void
{
    if (!$this->children->contains($child)) {
        $child->parent = $this;
        $child->parentId = $this->id;
        $this->children->add($child);
    }
}

Повторное тестирование

Для тестирования оптимизированной реализации CategoryRepositoryInterface нам достаточно добавить второй набор данных в data provider  CategoryRepositoryTest::behaviorDataProvider():

public function behaviourDataProvider(): array
{
    return [
        [
            DefaultCategoryRepository::class,
            [
                'SELECT c0_.parent_id AS parent_id_0, c0_.uid AS uid_1, c0_.created_at AS created_at_2, c0_.updated_at AS updated_at_3, c0_.id AS id_4, c0_.name AS name_5, c0_.parent_id AS parent_id_6 FROM category c0_ WHERE c0_.uid LIKE ? ORDER BY c0_.uid ASC',
                'SELECT t0.parent_id AS parent_id_1, t0.uid AS uid_2, t0.created_at AS created_at_3, t0.updated_at AS updated_at_4, t0.id AS id_5, t0.name AS name_6, t0.parent_id AS parent_id_7 FROM category t0 WHERE t0.parent_id = ?',
                'SELECT t0.parent_id AS parent_id_1, t0.uid AS uid_2, t0.created_at AS created_at_3, t0.updated_at AS updated_at_4, t0.id AS id_5, t0.name AS name_6, t0.parent_id AS parent_id_7 FROM category t0 WHERE t0.parent_id = ?'
            ]
        ],
        [
            ImprovedCategoryRepository::class,
            [
                'SELECT c0_.parent_id AS parent_id_0, c0_.uid AS uid_1, c0_.created_at AS created_at_2, c0_.updated_at AS updated_at_3, c0_.id AS id_4, c0_.name AS name_5, c0_.parent_id AS parent_id_6 FROM category c0_ WHERE c0_.uid LIKE ? ORDER BY c0_.uid ASC',
            ]
        ]
    ];
}

Таким образом, мы ожидаем, что после загрузки коллекции сущностей с помощью метода ImprovedCategoryRepository::findDescendantsByUid() дальнейшие манипуляции над вложенными коллекциями не будут инициировать дополнительные запросы в БД. Убедимся в этом, повторно запустив тест:

PHPUnit 9.5.27 by Sebastian Bergmann and contributors.

..                   2 / 2 (100%)

Time: 00:00.100, Memory: 10.00 MB

OK (2 tests, 4 assertions)
Process finished with exit code 0

Вуаля, наш “фикс” сработал! На больших деревьях эффект будет куда заметнее, т.к. вместо сотен или тысяч запросов будет выполняться лишь один: проверено на практике. Надеюсь, что статья была полезной для вас.