Symfony 2 Doctrine COUNT

Question

I have in my table "Artiste" one column "valideAdmin" who takes value 1 or 0.

I try to make a simple count to return the number of entries in my table where "valideAdmin" is to 1:

    $repo = $this   ->getDoctrine()
                    ->getManager()
                    ->getRepository('ProjectMainBundle:Artiste');

    $qb = $repo->createQueryBuilder('valideAdmin');
    $qb->select('COUNT(valideAdmin)');
    $qb->where('valideAdmin=1');

    $count = $qb->getQuery()->getSingleScalarResult();

    return array(
        'count' => $count
    );

But it always "1" who's return...

Without where clause, I have the total count of the entries of the table, but valideAdmin can be 0 or 1. I only want the count number where valideAdmin=1

Thanks for help

Answer 1

createQueryBuilder()'s first parameter is the alias that you want your entity to take (ie.: a short name to be used to refer to your entity in the query).

What you need to do is set a proper alias for your entity (for example a for Artiste) and then COUNT() the instances of your entity where the property (not the column) valideAdmin is set to one:

$repo = $this   ->getDoctrine()
                ->getManager()
                ->getRepository('ProjectMainBundle:Artiste');

$qb = $repo->createQueryBuilder('a');
$qb->select('COUNT(a)');
$qb->where('a.valideAdmin = :valideAdmin');
$qb->setParameter('valideAdmin', 1);

$count = $qb->getQuery()->getSingleScalarResult();

Remember that DQL runs queries on entities. The DQL your write is then translated into SQL to query the underlying data source after.