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.

score 17 · Accepted Answer · answered Dec 02 '15 at 15:45

17

wrap-queries solved this problem

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

answered Dec 02 '15 at 15:45

gggg

283
3
9

1

HI @gggg great news! Can you link some doc about this params? Can you mark your answer as accepted so you can close your question? – Matteo Dec 02 '15 at 16:27
1

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']; }` – scandel Feb 13 '21 at 14:01
Same here as @scandel, I really didn't know where to officially look for this one. Thanks. – Graph Dec 17 '21 at 13:07

score 2 · Answer 2 · edited Jun 29 '20 at 15:15

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

Note ['distinct' => false] option, without it I got error. – Nuryagdy Mustapayev Jun 29 '20 at 09:21 — Nuryagdy Mustapayev, Jun 29 '20 at 09:21

score 1 · Answer 3 · answered Jun 29 '20 at 09:29

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

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

3 Answers3