教义:使用左连接进行分页 [英] Doctrine : Pagination with left Joins

问题描述

我想对至少有 2 个左连接的复杂请求进行分页，但是我使用的分页包 (KnpPaginationBundle) 无法告诉 Doctrine 如何计算结果 (这是分页过程所需要的)，并继续出现此异常.>

无法统计选择两个FROM组件的查询，无法区分

这是使用 Doctrine QueryBuilder 构建的示例请求.

公共函数 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);如果 ($getQuery == true) {返回 $query;}返回 $query->getQuery()->getResult();}

然后我把这个请求交给 knp_paginator 服务，然后我得到了异常

 $groupQuery = $this->em->getRepository('MyBundle:GroupMemberRel')->findGroupsByUser($user, $listFilter, true);$paginator = $this->container->get('knp_paginator');/* @var $groups KnpComponentPagerPaginationPaginationInterface */$groups = $paginator->paginate($groupQuery, $this->container->get('request')->query->get('page', 1), 10/* 每页限制 */);

关于如何对复杂请求进行分页的任何想法，我很确定这个用例很常见，不想在分页后对我的结果进行补充.

解决方案

对于任何正在寻找有关此问题的答案的人，有一个很好的解决方案:https://github.com/KnpLabs/KnpPaginatorBundle/blob/master/Resources/doc/manual_counting.md

$paginator = 新分页器；//确定整个结果集的计数$count = $entityManager->createQuery('SELECT COUNT(c) FROM EntityCompositeKey c')->getSingleScalarResult();//创建一个与分页器一起使用的查询，并指定一个提示$query = $entityManager->createQuery('SELECT c FROM EntityCompositeKey c')->setHint('knp_paginator.count',$count);//分页，并将disctinct"选项设置为 false$pagination = $paginator-> paginate($查询，1、10、大批('不同' =>错误的，));

基本上，您正在做的是创建自己的计数"查询并指示 knp 分页器使用它.

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 KnpComponentPagerPaginationPaginationInterface */
    $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;

// determine the count of the entire result set
$count = $entityManager
    ->createQuery('SELECT COUNT(c) FROM EntityCompositeKey c')
    ->getSingleScalarResult();

// create a query to be used with the paginator, and specify a hint
$query = $entityManager
     ->createQuery('SELECT c FROM EntityCompositeKey c')
     ->setHint(
        'knp_paginator.count', 
        $count
    );

// paginate, and set "disctinct" option to false
$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.

这篇关于教义:使用左连接进行分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！