Symfony2: count from the querybuilder

Question

I would like to know the number of row which are in my table TABLE and for which attribute name="joe"

Here the code I use so far but I retrieve the objects (which is just unnecessary, so not optimized)

$repository = $this->getDoctrine()->getManager()->getRepository('MyBundle:TABLE');
$name = "joe";
$liste = $repository->findBy(array('name' => $name));
$nombre = count($liste);

how can I achieved it with querybuilder using count? need to set parameter $name. All I ve seen so far have no parameter like this one, so do not know how this can work... (moreover I would like to avoid using paginator)

thanks.

Answer 1

You can achieve it this way:

$repository = $this->getDoctrine()->getManager()->getRepository('MyBundle:TABLE');
$name = "joe";
$qb = $repository->createQueryBuilder('t');
$qb->select('count(t.id)');
$qb->where('t.name = :name');
$qb->setParameter('name', $name);
$nombre = $qb->getQuery()->getSingleScalarResult();

But good practice is to put this logic into repository class, so you can call method like this:

$nombre = $repository->countByName($name); 

Just create new method in your TableRepository class:

public function countByName($name)
{
    $qb = $this->createQueryBuilder('t');
    $qb->select('count(t.id)');
    $qb->where('t.name = :name');
    $qb->setParameter('name', $name);

    return $qb->getQuery()->getSingleScalarResult();
}