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.

Answer 1

wrap-queries solved this problem

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

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

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