Symfony2 Doctrine error: Cannot count query that uses a HAVING clause. Use the output walkers for pagination

Question

I am trying to get collections that are non-empty, i.e. have at least 1 object. Collection entity has OneToMany relationship with Object entity. I am using KNP paginator to paginate result. This is my function:

  public function fetchAction(Request $request){
    $em = $this->getDoctrine()->getManager();

    $page = $request->get('page', 1);
    $limit = 10;

    $collections = $em->createQueryBuilder()
        ->select('c')
        ->add('from', 'CollectionBundle:Collection c LEFT JOIN c.object o')
        ->having('COUNT(o.id)>0')
        ->orderBy('c.date', 'DESC')
        ->getQuery();

    $collections = $this->get("knp_paginator")->paginate($collections, $page, $limit);

    return $this->render('CollectionBundle:Collection:fetch.html.twig', [
        'collections' => $collections
    ]);
}

Error

I keep getting following error

 Cannot count query that uses a HAVING clause. Use the output walkers for pagination

Without 'Having' clause everything works fine, but I must get non-empty collections.

Azoic

Answer 1

wrap-queries solved this problem

 $collections = $this->get("knp_paginator")->paginate($collections, $page, $limit,array('wrap-queries'=>true));

Azoic

HI @Azoic great news! Can you link some doc about this params? Can you mark your answer as accepted so you can close your question? – Chiao

Had the same problem 6 years after, your answer solved it for me... thanks. There is not much documentation on this option, when looking inside the code of KNP components, you find it, it just enables the use of an OutputWalker: .../vendor/knplabs/knp-components/src/Knp/Component/Pager/Event/Subscriber/Paginate/Doctrine/ORM/QuerySubscriber.php : $useOutputWalkers = false; if (isset($event->options['wrap-queries'])) { $useOutputWalkers = $event->options['wrap-queries']; } – Timework

Same here as @scandel, I really didn't know where to officially look for this one. Thanks. – Handgun

Answer 2

You can implement the Manual counting, as described here in the doc.

As example, you can modify your code as follow:

$count = $em->createQueryBuilder()
        ->select('COUNT(c)')
        ->add('from', 'CollectionBundle:Collection c LEFT JOIN c.object o')
        ->having('COUNT(o.id)>0')
        ->orderBy('c.date', 'DESC')
        getSingleScalarResult();


    $collections = $em->createQueryBuilder()
        ->select('c')
        ->add('from', 'CollectionBundle:Collection c LEFT JOIN c.object o')
        ->having('COUNT(o.id)>0')
        ->orderBy('c.date', 'DESC')
        ->getQuery();

    $collections->setHint('knp_paginator.count', $count); 

    $collections = $this->get("knp_paginator")->paginate($collections, $page, $limit,array('distinct' => false));

    return $this->render('CollectionBundle:Collection:fetch.html.twig', [
        'collections' => $collections
    ]);

Hope this help

Chiao

Note ['distinct' => false] option, without it I got error. – Lepanto

Answer 3

My solution is based on @Matteo's solution, since my query was a bit complicated I wanted to share my version also:

$qb = $this->createQueryBuilder('c');
    $qb->select('count(c.id)')
        ->addSelect('COUNT(DISTINCT m.id) AS HIDDEN messageCount')
        ->addSelect('COUNT(DISTINCT f.id) AS HIDDEN fileCount')
        ->join('c.user', 'u')
        ->join('c.status', 's')
        ->join('c.company', 'comp')
        ->leftJoin('c.files', 'f')
        ->leftJoin('c.messages', 'm');

    $this->_set_filters($filter, $qb);
    $qb->groupBy('c.id');
    $countQuery = $qb->getQuery();

    /** wrap query with SELECT COUNT(*) FROM ($sql)
    * I don't know what exactly does this block but
    * I coppied it from Doctrine\ORM\Tools\Pagination\Paginator::getCountQuery()
     */
    $platform = $this->getEntityManager()->getConnection()->getDatabasePlatform();
    $rsm = new Query\ResultSetMapping();
    $rsm->addScalarResult($platform->getSQLResultCasing('dctrn_count'), 'count');
    $countQuery->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, CountOutputWalker::class);
    $countQuery->setResultSetMapping($rsm);

    return $countQuery->getSingleScalarResult(); //returns integer

Lepanto