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.

Mikhail Prosalov · Accepted Answer · 2014-12-17 19:00:45Z

8

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();
}

edited Dec 17, 2014 at 19:00

answered Dec 17, 2014 at 18:55

Mikhail Prosalov

4,4234 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tvde1 Over a year ago

Why would you use count(t.id) over count('*')?

Mikhail Prosalov Over a year ago

There shouldn't be a major performance difference in this case (when query have WHERE clause with non-indexed field). But yes, count(*) is a slightly better, for example in case of using MyISAM storage engine.

Collectives™ on Stack Overflow

Symfony2: count from the querybuilder

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related