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?

score 2 · Answer 1 · answered Jun 07 '15 at 18:49

If your CategoryRepository inherits from NestedTreeRepository you could do something like this:

$categories = $em->getRepository('XBundle:Category')
  ->childrenQueryBuilder($category)
  ->addSelect('posts')
  ->join('node.posts', 'posts')
  ->getQuery()
  ->getResult();

foreach ($categories as $category) {
  $category->getPosts();
  // do stuff
}

score 2 · Answer 2 · answered Jun 08 '15 at 04:29

2

You should be able to do this in one query which will be close to this one as i am not pro SQL it usually takes me time and tests before i get it right but this is where i would start :

 SELECT parent.* , children.* FROM 
          (SELECT p, c FROM AppBundle:Post p JOIN p.category c WHERE c.slug = :category) AS parent 
          INNER JOIN 
          (SELECT p1 FROM  AppBundle:Post p1 JOIN p.category c1 ON c1.parent = parent.id ) AS children

not sure if you need to do the ON inside the inner select or the wrapper select for the join but you can try :)

answered Jun 08 '15 at 04:29

Nawfal Serrar

2,213
1
14
22

It gives a semantical error with '('. I'll keep trying different solutions with SQL, at least now I can focus there. Thanks! – Diego Jun 08 '15 at 07:43
I knew it would give some kind of error haha but this is the way with sub query better than using a loop. – Nawfal Serrar Jun 08 '15 at 07:49

score 0 · Accepted Answer · answered Feb 16 '16 at 20:21

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

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

3 Answers3