Doctrine2. Как выбрать и гидрировать связанные коллекции?

Question

[liongen]

Есть задача задача следующего вида:

Имеется сущность Category, к которой через связь OneToMany привязана сущность Post, которая в свою очередь, также имеет связь OneToMany с сущностью Comment

Нужно: выбрать 10 категорий, у которых будут содержаться 20 постов отсортированные в обратном порядке по id, у каждой из которых также будут 10 комментов, отсортированные по дате добавления.

Получается всего 3 запроса, что то типа:

SELECT *.c FROM 'category'.'c' ORDER BY id DESC LIMIT 10;
SELECT *.p FROM 'post'.'p' WHERE 'p'.'category_id' IN (2, 3, 5, 7, 9) ORDER BY id DESC LIMIT 20;
SELECT *.c FROM 'comment'.'c' WHERE 'c.'post_id' IN (56, 42, 345, 223, ...) ORDER BY created_at DESC LIMIT 10;

На Yii2/Laravel5 такие задачи решаются автоматически, через ActiveRecord.

Предполагаю, что надо разбить каждый из запросов в отдельный репозиторий (что то вроде findCommentsByPosts($posts = []), но как в итоге это все связать воедино, чтобы, например при рендере в твиге можно было к коллекциям внутри коллекций обращаться:

{% for category in categories %}
    {% for post in category.posts %}
         {% for comment in post.comments %}
              {{ comment.content }}
         {% endfor %}
    {% endfor %}
{% endfor %}

Может, есть лучше способ, как решают такие проблемы в Doctrine2?

Натыкался на решения подобно этому: https://phpclub.ru/talk/threads/doctrine-collectio...
Но чувствую, что это скорее крик безысходности.

Answer 1

[BoShurik]

Если я правильно понял вопрос
docs.doctrine-project.org/projects/doctrine-orm/en...

Comments

[liongen]

К сожалению не подходит. Это может работать, если у нас один объект содержит коллекцию других сущностей, но если у меня допустим 5 категорий и мне нужно для каждой по особому выбрать посты (а у них еще и комменты в определенном порядке), то это нужно протерировать каждую сущность и выполнить у каждой запрос к БД

[BoShurik]

{% for category in categories %}
    {% for post in category.lastPosts %}
         {% for comment in post.lastComments %}
              {{ comment.content }}
         {% endfor %}
    {% endfor %}
{% endfor %}

Методы Category::getLastPosts и Post::getLastComments будут матчить критерии на основную коллекцию posts и comments соответственно

Или я не так понял?

P.S. В вашем примере

SELECT *.p FROM 'post'.'p' WHERE 'p'.'category_id' IN (2, 3, 5, 7, 9) ORDER BY id DESC LIMIT 20;

вы можете получить посты только из одной категории. Так и задумано?

[liongen]

BoShurik, смотрите, если у меня есть коллекция категорий состоящая из 5 штук, при итерации этой коллекции и обращению к category.lastPosts в твиге, будет идти запрос в БД за списком постов, итого 1 запрос на выборку категорий + 5 запросов на выборку постов для каждой = 6 запросов. А если пойти еще глубже?

вы можете получить посты только из одной категории. Так и задумано?

Почему из одной? Как минимум из 5, может я что то пропустил?

[BoShurik]

liongen, если у вас последние 20 постов будут принадлежать 2-ой категори, то вы получите посты только из нее. А судя по вопросу вы хотите по 20 последних постов в каждой категории

[liongen]

BoShurik, верно. Этот SQL с ошибкой, я его указал для примера. Так например делает ORM в Laravel в режиме жадной загрузки: сначала выбираются категории из БД, из них собираются идентификаторы для постов и уже они одним запросом забираются из БД, после этого посты мапятся коллекциями к категориям.

На DQL этот запрос бы выглядел как то так:

$query = $em->createQuery('SELECT p FROM AppBundle\Entity\Post p WHERE p.category_id IN (?1) ORDER BY p.id DESC');
        $query->setMaxResults(20);
        $query->setParameter(1, $categoriesIds);
        $posts = $query->getResult();

Я специально не стал описывать получение $categoriesIds

[BoShurik]

liongen, тот запрос, что вы привели имеет такой же недостаток :) Если вы хотите сделать одним запросом, то тут нужны пользовательские переменные. И сомневаюсь, что доктрина так умеет

[BoShurik]

liongen, что-то вроде этого:

SELECT * FROM (
    SELECT c.id AS cid, p.id AS pid, FROM categories AS c LEFT JOIN posts AS p ON c.id = p.category ORDER BY c.id, p.id DESC
) AS q
WHERE IF (@cid=q.cid, @a:=@a+1, (@cid:=q.cid) AND (@a:=1)) <= 20;

[BoShurik]

liongen, Как вариант применить на этот запрос ResultSetMapping (docs.doctrine-project.org/projects/doctrine-orm/en...

[liongen]

тот запрос, что вы привели имеет такой же недостаток :) Если вы хотите сделать одним запросом, то тут нужны пользовательские переменные. И сомневаюсь, что доктрина так умеет

BoShurik, так и не понял почему этот запрос не отработает) только что проверил - все ок, кстати сама доктрина такие запросы строит если в fetch (или setFetchMode()) прописать EAGER (но работает вроде только для связей ManyToOne или ManyToMany)

[BoShurik]

Если вам нужно последний 20 записей из категорий $categoriesIds, то такой запрос отработает. Если вам нужно по 20 записей из каждой категории, то нужно использовать запрос выше

[liongen]

BoShurik, а ведь верно) до меня только дошло) спасибо!)

[liongen]

BoShurik

{% for category in categories %}
    {% for post in category.lastPosts %}
         {% for comment in post.lastComments %}
              {{ comment.content }}
         {% endfor %}
    {% endfor %}
{% endfor %}

этот пример работает как надо! видимо я и сам не до конца понял всю проблематику вопроса..( конечно создается огромное количество запросов к БД, но по другому и не выбрать такую коллекцию данных (пользовательские переменные не в счет, в постгресе например их вовсе нет). Теперь я вот думаю, а это нормально иметь такие методы в entity:

public function getLatestPosts()
    {
        $collection = $this->getPosts();
        $criteria = Criteria::create()
            ->orderBy(["id" => Criteria::DESC])
            ->setFirstResult(0)
            ->setMaxResults(20);

        return $collection->matching($criteria);
    }

Что, если у меня добавятся параметры, например направление сортировки или максимальное кол-во измениться?

[BoShurik]

liongen, в документации так и рекомендуют

You can move the access of slices of collections into dedicated methods of an entity. For example Group#getTodaysBirthdayUsers()

Что, если у меня добавятся параметры, например направление сортировки или максимальное кол-во измениться?

В Symfony Best Practice рекомендуют такие вещи выносить в константы. При желании можно в метод доп параметры пробрасывать, так что это не проблема

Answer 2

[novrm]

В репозитории сущности Category создаете метод типа getCategory, где создаете подобный запрос.
Вот только сортировка и количество разных под-коллекций одним запросом не получится.
Как вариант - потом делать пагинацию и фильтрацию полученного результата.

public function getCategory()
{
        $queryBuilder = $this->getEntityManager()->createQueryBuilder();

        $queryBuilder
        ->select('category')->from($this->getClassName(), 'category')
        ->addSelect('c_categoryHasPosts')->leftJoin('category.categoryHasPosts', 'c_categoryHasPosts')
            ->addSelect('c_cHasP_post')->leftJoin('c_categoryHasPosts.post', 'c_cHasP_post')
                ->addSelect('c_cHasP_p_postHasComments')->leftJoin('c_cHasP_post.postHasComments', 'c_cHasP_p_postHasComments')
                    ->addSelect('c_cHasP_p_pHasC_comment')->leftJoin('c_cHasP_p_postHasComments.comment', 'c_cHasP_p_pHasC_comment')
        ->setParameter('categoryId', 1)
        ->setParameter('postId', 1)
        ->andWhere('c_cHasP_post.category_id = :categoryId')
        ->orWhere('c_cHasP_p_pHasC_comment.post_id = :postId')
        
        return $queryBuilder->getQuery()->getResult();
}

Comments

[BoShurik]

Гидрация этого дела будет очень долгой
ocramius.github.io/blog/doctrine-orm-optimization-...

The process of hydration becomes extremely expensive when more than 2 LEFT JOIN operations clauses are part of our queries:

[novrm]

BoShurik, Гидратировать очень даже легко.
Просто нужно знать как.

Я, например, работаю с Zend Framework+Doctrine+Twig.
Могу утверждать, что гидратировать можно легко коллекции любой вложенности.

Для гидратации коллекций в Doctrine существуют стратегии:
DoctrineModule/src/DoctrineModule/Stdlib/Hydrator/...

[liongen]

novrm, Вы используете свои стратегии для гидрирования? Странно, Ocramius указал в том посте, что помогает только мульти шаговая гидрация с жадной выборкой сначала одной коллекции, а потом другой (в посте есть примеры). Клонировал его репу и реально скорость гидрации с двумя джойнами в одном запросе падает в разы. В любом случае, его кейс не подходит. Я задал ему вопрос лично, с примером такого кейса из этого вопроса, он ответил что только тремя запросами надо делать. Я в принципе и сам понимаю что нужно делать разные запросы, но как потом это все объединить в одну коллекцию с вложенными коллекциями на доктрине ума не приложу.

P.S. кстати смотрю люди не всегда заморачиваются такой оптимизацией (достаточно популярный проект во Франции) https://github.com/EnMarche/en-marche.fr/blob/deve...

[novrm]

liongen, Не знаю, что там говорит Ocramius, но у меня есть полностью рабочий проект.
Кроме того я не уверен, что вы понимаете суть гидратации.
Это (гидратация) нужно исключительно для ФОРМ - а конкретно для работы с БД через поля ФОРМ.

Если вы желаете ИЗВЛЕКАТЬ данные из БД и выводить их "in View" в виде неких списков - то гидратация вам не нужна.
Вы можете спокойно занести все данные в БД посредством того же phpMyAdmin.
Потом сделать, например - findAll() и получать ВСЕ значения любого уровня коллекций через foreach.
... или в Twig - через for in.

[liongen]

Кроме того я не уверен, что вы понимаете суть гидратации.

Если смотреть в контексте доктрины, гидрация (hydration) - процесс преобразования результирующего набора данных (для примера, полученных из БД) в определенную структуру (например обьект или массив).

Если вы желаете ИЗВЛЕКАТЬ данные из БД и выводить их "in View" в виде неких списков - то гидратация вам не нужна.

я вам говорю не о выводе простых списков, а о выводе иерархии

[novrm]

liongen, А списки это не иерархия?
Ладно не нравится вам списки - пускай будет иерархия.

Вот вам схема


Так получаете запрос с БД

$entityManager = $this->getServiceManager()->get('Doctrine\ORM\EntityManager');
$categoryRepository = $entityManager->getRepository('Database\Entity\Category');
$testCategories = $categoryRepository->findAll();

$viewModel->setVariables(['testCategories' => $testCategories]);

Так выводите всю иерархию в Вид

<ol>
    {% for testCategory in testCategories %}
    <li>
        {{ testCategory.getShortName() }}
        <ol>
            {% for categoryHasProduct in testCategory.getCategoryHasProducts() %}
            <li>
            {{ categoryHasProduct.getProduct().getShortName() }}
            </li>
            {% endfor %}
        </ol>
    </li>
    {% endfor %}
</ol>

[liongen]

novrm, так это же обычный lazy loading, на каждый {{ categoryHasProduct.getProduct().getShortName() }} будет улетать запрос в БД, а если у меня таких сотня на страницу?

[novrm]

liongen, Я дал вам ответ изначально.
Вместо findAll - используйте $queryBuilder и leftJoin.
Так вы извлечете сразу ВСЕ нужное ОДНИМ запросом.

[liongen]

novrm, это решило бы мою проблему (разве что таблички для джойна не стали бы за миллион записей), но я так и не понял, как у вас получается гидрировать кучу однородных объектов без потерь. Посмотрите ту статью, что скинул BoShurik там подробно описана эта ситуация. Еще советую склонировать репу https://github.com/Ocramius/Doctrine2StepHydration и поиграться с single-fetch-join.php и multi-step-hydration.php

[novrm]

liongen, Табличек Join может быть не более 61.
О каких потерях вы говорите?
Один запрос с множествами Join это не lazy loading - где каждый запрос отдельно.

У вас есть инструмент для тестирования КОЛИЧЕСТВА запросов к БД при загрузки вида?

-------------
Да - миллион записей - это уже другой подход нужен.
Отдельные запросы с LIMIT.

[Алексей Скобкин]

novrm, Вам говорят о том, что Doctrine плохо справляется с гидрацией результатов тех запросов, где более одного Join.

[novrm]

Алексей Скобкин, Это разве ко мне претензия?
пишите разработчикам Doctrine...
... или используйте голый SQL.
Вы спрашивали как с Doctrine - я вам ответил.

[Алексей Скобкин]

novrm, Я не спрашивал.
Вы выше дали ответ, под которым человек написал, что будут проблемы и указал, в чём именно. Там же написано, как с этим бороться. А вы ответили, судя по всему, даже не ознакомившись с материалом по ссылке. И я так понимаю, liongen именно это и обескуражило. Потому что речь шла про гидрацию, а вы начали что-то про деревья и вывод в слое представления. Да и вообще комментарий, скорее, был адресован не столько вам, сколько человеку, который писал вопрос. Потому что он возьмёт ваше решение и у него будет тормозить проект. А так он увидит ещё ссылку на статью от Марко и будет знать как это оптимизировать.

пишите разработчикам Doctrine...

Разработчик Doctrine в статье по ссылке в первом комментарии как раз пишет о проблеме и о том, как с ней бороться.

[novrm]

Алексей Скобкин, То есть вот такое решение с миллионом запросов в БД лучше одного запроса с join?
Вот какой ответ - решил принять за правильный liongen благодаря вашим советам:

конечно создается огромное количество запросов к БД, но по другому и не выбрать такую коллекцию данных (пользовательские переменные не в счет, в постгресе например их вовсе нет). Теперь я вот думаю, а это нормально иметь такие методы в entity:

И что тут он оптимизировал?
Любой школьник сможет так решить проблему - максимально увеличив количество запросов :)

[liongen]

novrm, посмотрите внимательно на вопрос. Перефразирую, нужна коллекция А с коллекцией В у которой нужна коллекция С. У каждой из коллекций стоит своя сортировка и свой лимит. Сможете решить такое одним запросом?

Ну а что касается миллионов запросов к БД, то это надо решать уже на другом уровне (например кэшированием)

[novrm]

liongen, Вы обвиняете меня в невнимательности, причем сами никогда не читали моего первого ответа:

В репозитории сущности Category создаете метод типа getCategory, где создаете подобный запрос.
Вот только сортировка и количество разных под-коллекций одним запросом не получится.
Как вариант - потом делать пагинацию и фильтрацию полученного результата.

[Алексей Скобкин]

novrm,

То есть вот такое решение с миллионом запросов в БД лучше одного запроса с join?
Вот какой ответ - решил принять за правильный liongen благодаря вашим советам.

Кто ж виноват, что он тоже не сходил по ссылке и выбрал другую крайность? Если вы не заметили, он выбрал не то, что предлагает не то, что предлагал BoShurik (решение без миллиона запросов и сверхмедленной гидрации).

Любой школьник сможет так решить проблему - максимально увеличив количество запросов

Запрос на коллекцию - не миллион запросов. Или у вас миллион коллекций?

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

[novrm]

Алексей Скобкин, В любом случае эта ситуация печальна.
Ибо на лицо явная коллизия между рекомендацями приводить все таблицы БД к нормальному виду для избежания дублей (для этого нужно создавать связи -toOne и -toMany) - это раз...
... два - извлекать нужные данные ОДНИМ запросом...
... и избыточном извлечении таких связей Doctrine.

Что пишет Ocramius:

Therefore, the simplest yet most limiting advice is to avoid collection-valued associations whenever they are not strictly necessary.

Это же крамола. Выбивать из под ног разработчика самый мощный инструмент оптимизации записей в БД.
Кроме того - то, что предлагает Ocramius оно несомненно в простейшем случае уменьшает время получения данных из БД...
... но что в замен - куча избыточного запутанного кода, результат которого еще труднее связать вместе.

------------------
С моей точки зрения - решение - проще код с максимальной связанностью и с нормализацией таблиц БД (оппозиция к Ocramius)....
... ну и конечно - более мощный сервис для возможности проведения таких операций с БД.

[Алексей Скобкин]

... и избыточном извлечении таких связей Doctrine.

Это не коллизия. Это особенность дефолтных гидраторов Doctrine. Они с этим тяжело справляются. Поэтому можно оптимизировать это так, как указано по той ссылке. Ситуация нормальна. Нет универсальных инструментов, которые всё делают идеально.

... но что в замен - куча избыточного запутанного кода, результат которого еще труднее связать вместе.

Зависит от ситуации. Он отнюдь не всегда запутан и нечитаем. К тому же это типичная приоритизация по трём факторам: "нужно ORM", "нужно быстро", "нужно красиво". Можно получить быстро и красиво, но без ORM. Можно получить быстро и с ORM, но некрасиво. Можно получить красиво на ORM, но медленно. Нужно выбрать, что для конкретного случая важнее.
По поводу сильно связанного кода я скорее на стороне Ocramius (и SOLID), но спорить об этом не хочу.