原则：与左连接分页 [英] Doctrine : Pagination with left Joins

问题描述

我想分页至少2个左连接的复杂请求，但是我使用的分页捆绑（KnpPaginationBundle）不能告诉Doctrine如何计算结果（），并保持此异常。 p>

不能计数选择两个FROM组件的查询，不能区分

这是一个使用Doctrine QueryBuilder构建的示例请求。

  public function findGroupsByUser $ user，$ listFilter，$ getQuery = false，$ order ='ASC'）
 {
 $ query = $ this-> createQueryBuilder（'r'）
  - > select 'r as main，g'）
  - > select（'r as main，g，count（gg）as members'）
  - > leftjoin（'r.group'，'g' ）
  - > innerjoin（'MyBundle：Group 
  - > addGroupBy（'g.groupId'）
  - > add（'orderBy''，'gg' g.name'。 $顺序）; 
 if（$ getQuery == true）{
 return $ query; 
} 
 
返回$ query-> getQuery（） - > getResult（）; 
} 
  

然后我向knp_paginator服务发出此请求，然后我已经例外

  $ groupQuery = $ this-> em-> getRepository（'MyBundle： GroupMemberRel'） - > findGroupsByUser（$ user，$ listFilter，true）; 
 $ paginator = $ this-> container-> get（'knp_paginator'）; 
 / * @var $ groups Knp\Component\Pager\Pagination\PaginationInterface * / 
 $ groups = $ paginator-> paginate（
 $ groupQuery，$ this-> ;容器 - > get（'request'） - > query-> get（'page'，1），每页10 / *限制* / 
）;任何关于如何分页一个复杂的请求的想法，我很确定这个用例是这个用例的一个例子：
  

常见的，不想在分页后水化我的结果。

解决方案

对于任何寻找答案的人来说，一个很好的解决方案：
https：// github。 com / KnpLabs / KnpPaginatorBundle / blob / master / Resources / doc / manual_counting.md

  $ paginator = new Paginator ; 
 
 $ count = $ entityManager 
  - > createQuery（'SELECT COUNT（c）FROM Entity\CompositeKey c'）
  - > getSingleScalarResult（）; 
 
 $ query = $ entityManager 
  - > createQuery（'SELECT c FROM Entity\CompositeKey c'）
  - > setHint（'knp_paginator.count'，$ count ）; 
 
 $ pagination = $ paginator-> paginate（$ query，1，10，array（'distinct'=> false））; 
  

基本上，你在做什么，是创建自己的'count'查询并指示knp paginator使用这个。不要忘了添加

I would like to paginate a complex request with at least 2 left joins, but the pagination bundle I'm using (KnpPaginationBundle) can't tell Doctrine how to count the result (which is needed for the pagination process), and keep having this Exception.

Cannot count query which selects two FROM components, cannot make distinction

Here is a sample request built with the Doctrine QueryBuilder.

public function findGroupsByUser(User $user, $listFilter, $getQuery = false, $order = 'ASC')
{
    $query = $this->createQueryBuilder('r')
        ->select('r as main,g')
        ->select('r as main,g, count(gg) as members')
        ->leftjoin('r.group', 'g')
        ->innerjoin('MyBundle:GroupMemberRel', 'gg', 'WITH', 'r.group = gg.group')
        ->addGroupBy('g.groupId')
        ->add('orderBy', 'g.name ' . $order);
   if ($getQuery == true) {
        return $query;
    }

    return $query->getQuery()->getResult();
}

Then I give this request to the knp_paginator service, and then I've got the exception

    $groupQuery = $this->em->getRepository('MyBundle:GroupMemberRel')->findGroupsByUser($user, $listFilter, true);
    $paginator = $this->container->get('knp_paginator');
    /* @var $groups Knp\Component\Pager\Pagination\PaginationInterface */
    $groups = $paginator->paginate(
        $groupQuery, $this->container->get('request')->query->get('page', 1), 10 /* limit per page */
    );

Any idea on how to paginate over a complex request, I'm pretty sure this use-case is common, don't want to hydrate my result after the pagination.

解决方案

For anyone looking for an answer about this, there is a good solution at: https://github.com/KnpLabs/KnpPaginatorBundle/blob/master/Resources/doc/manual_counting.md

$paginator = new Paginator;

$count = $entityManager
->createQuery('SELECT COUNT(c) FROM Entity\CompositeKey c')
->getSingleScalarResult();

$query = $entityManager
->createQuery('SELECT c FROM Entity\CompositeKey c')
->setHint('knp_paginator.count', $count);

$pagination = $paginator->paginate($query, 1, 10, array('distinct' => false));

Basically, what you are doing, is you are creating your own 'count' query and instruct knp paginator to use this. Do not forget to add

这篇关于原则：与左连接分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！