Get children's posts in parents with Doctrine Extensions Tree Nested set

Question

I'm using the nested set behaviour in Symfony2 with StofDoctrineExtension.

The category and post model are well configured, and the category tree works fine.

To show the posts of a category I use this query from my repository:

public function findAllPosts($category)
{
    return $this->queryAllPosts($category)->getResult();
}

public function queryAllPosts($category)
{
    $em = $this->getEntityManager();

    $query = $em->createQuery('
        SELECT p, c FROM AppBundle:Post p JOIN p.category c
        WHERE c.slug = :category
        ORDER BY p.created DESC
    ');

    $query->setParameter('category', $category);

    return $query;
}

But how could I do to show the posts of the children of the categories too?

Answer 1

I found the way. The query would be like this:

/*
 * GET POSTS FROM PARENT AND CHILDREN
 */
public function getPostsParentAndChildren($children)
{
    $em = $this->getEntityManager();

    $posts = $em->createQueryBuilder()
        ->select(array('p', 'c'))
        ->from('AppBundle:Post', 'p')
        ->join('p.category', 'c')
        ->where('c.id IN (:children)')
        ->orderBy('p.created', 'DESC')
        ->getQuery();

    $posts->setParameter('children', $children);

    return $posts->getResult();
}

We pass an array with the children to the query, which we obtain with the function getChildren($categoryId). Remember that you have to pass the id (with this query), so you could get the ids like this:

    $category = $repo->findOneBy(array('slug' => $slug1));

    $children = $repo->getChildren($category);

    $childrenIds[] = $category->getId();
    foreach ($children as $child){
        $id = $child->getId();
        $childrenIds[] = $id;
    }

    $posts = $em->getRepository('AppBundle:Category')->getPostsParentAndChildren($childrenIds);