Filter with a function count()

Question

I try to filter with a count() but I have a problem in my display.

I get this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sclr_0' in 'where clause'

Thanks a lot for your help

My request is :

public function findAllVisibleQuery(FilterUser $filter)
{
    $query= $this->createQueryBuilder('u')
    ->leftJoin('u.commandes', 'cmd')
    ->leftJoin('u.zoneLivraison', 'zone')
    ->addSelect('cmd')
    ->addSelect('zone')
    ->Select(' COUNT(cmd.id) as nbCmd')
    ->GroupBy('u');

    if(!is_null($filter->getDateDebut()) && !is_null($filter->getDateFin()) && $filter->getDateFin() > $filter->getDateDebut()){
        $query=$query->Where('cmd.dateCommande BETWEEN :dateDebut AND :dateFin')
                     ->setParameter('dateDebut', $filter->getDateDebut())
                     ->setParameter('dateFin', $filter->getDateFin());

    }elseif (!is_null ($filter->getModePaiement())){
        $query=$query->andWhere('cmd.modePaiement = :modePaiement')
            ->setParameter('modePaiement', $filter->getModePaiement());

    }elseif (!is_null($filter->getZoneLivraison())){
        $query=$query->andWhere('zone.nom = :nom')
            ->setParameter('nom', $filter->getZoneLivraison());


   }elseif (!is_null($filter->getNbCommande())){
    $query=$query->andWhere('nbCmd < = :nbCmd')

    ->setParameter('nbCmd', $filter->getNbCommande());

    }

   return $query
       ->getQuery()
       ->getResult();

}

controller :

       public function listAction(Request $request)
       {

    $filter = new FilterUser();
    $form = $this->createForm(FilterUserType::class, $filter);
    $form->handleRequest($request);
    $users = $this
            ->getDoctrine()
            ->getManager()
            ->getRepository('CeUtilisateurBundle:User');
            $listUsers= $users->findAllVisibleQuery($filter);

            return $this->render('CeUtilisateurBundle:Utilisateur:list.html.twig', array(
        'form' => $form->createView(),
        'listUsers' => $listUsers,
    ));
}

Answer 1

Add required fields of joined entity instead of below code:

  ->addSelect('cmd')
  ->addSelect('zone')

use like

->addSelect('cmd.id')
->addSelect('zone.id')

and use also

->getSingleScalarResult();

instead of

->getResult();