Doctrine2. How to select and hydrate related collections?

Question

symfony

There is a task of the following type:
There is a Category entity, to which the Post entity is attached through the OneToMany connection, which in turn also has a OneToMany connection with the Comment entity
. You need to: select 10 categories that will contain 20 posts sorted in reverse order by id, each of which will also have 10 comments sorted by date added.
It turns out only 3 requests, something like:

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;

On Yii2/Laravel5, such tasks are solved automatically via ActiveRecord.
I suppose that you need to break each of the requests into a separate repository (something like findCommentsByPosts($posts = []), but how in the end to tie it all together, so that, for example, when rendering in twig, you can access collections inside collections:

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

Maybe there is a better way to solve such problems in Doctrine2?
I came across solutions like this: https://phpclub.ru/talk/threads/doctrine-collectio...
But I feel that this is more of a cry of desperation.

Answer 1

If I understood the question
correctly docs.doctrine-project.org/projects/doctrine-orm/en...

Answer 2

In the repository of the Category entity, you create a getCategory type method, where you create a similar request.
That's just sorting and the number of different sub-collections with one request will not work.
As an option - then do pagination and filtering the result.

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();
}