我在使用StofDoctrineExtension的Symfony2中使用嵌套集行为。
分类和post模型配置良好，分类树工作良好。
要显示一个类别的帖子，我使用来自我的存储库的这个查询:
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;
}
但是我怎么才能显示子类的帖子呢?
如果你的CategoryRepository继承自NestedTreeRepository，你可以这样做:
$categories = $em->getRepository('XBundle:Category')
->childrenQueryBuilder($category)
->addSelect('posts')
->join('node.posts', 'posts')
->getQuery()
->getResult();
foreach ($categories as $category) {
$category->getPosts();
// do stuff
}
你应该能够做到这一点，在一个查询将接近这一个，因为我不是专业的SQL，它通常需要我的时间和测试之前，我得到它的权利，但这是我将开始:
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
不确定是否需要在内部选择或包装器选择中进行连接，但您可以尝试:)
我找到了路。查询应该是这样的:
/*
* 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();
}
将包含子元素的数组传递给查询，该数组通过getChildren($categoryId)函数获得。请记住，您必须传递id(使用此查询)，因此您可以获得这样的id:
$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);