Symfony2 Doctrine Error: Unable to count a query that uses a HAVING clause. Use output walkers to paginate

Question

I am trying to get non-empty collections, i.e. have at least 1 object. A Collection object has a OneToMany relationship with an Object. I am using paginator KNP for 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
    ]);
}

Mistake

I keep getting the following error

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

Without the Availability clause, everything works fine, but I have to get non-empty collections.

Answer 1

wrap-query solved this problem

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

Answer 2

, , .

:

$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
    ]);