Doctrine 2: Count Items by Type in Select

Question

Does anyone know how to retrieve a count based on a condition in a Doctrine 2 QueryBuilder select() statement?

Here's what I've tried so far...

My first attempt was to try count() with eq(). The error I receive says "Expected close parentheses, got equals."

$qb->select($qb->expr()->count($qb->expr()->eq('t.id', '1')))

Next I tried count() with having(). The error I receive says "Maximum function nesting level reached."

$qb->select($qb->expr()->count($qb->having('t.id', '1')))

Then I tried count() with where() and eq(). Again I got "Maximum function nesting level reached."

$qb->select($qb->expr()->count($qb->where($qb->expr()->eq('t.id', '1'))))

Then I tried these variations using in(). They both give the syntax error "Expected FROM, got '('

$qb->select($qb->expr()->count($qb->expr()->in('t.id', array(1))))
$qb->select($qb->expr()->count($qb->expr()->in('t.id', 1)))

For the in() examples, I also tried passing in the value as a variable and via setParameter(), with the same result.

Here is the MySQL equivalent of what I'm trying to code in QueryBuilder:

SELECT
    SUM(IF(type.id = 1, 1, 0)) AS 'fords',
    SUM(IF(type.id = 2, 1, 0)) AS 'hondas'
FROM item
JOIN type ON item.type_id = type.id

Answer 1

Except if you need to stay with the DQL thing, this may help:

public function MyAction() {

    $this->doctrineContainer = Zend_Registry::get('doctrine');
    $em = $this->doctrineContainer->getEntityManager(); 

    $fords = $em->getRepository('My\Entity\Item')->findBy(array('type' => '1'));
    $hondas = $em->getRepository('My\Entity\Item')->findBy(array('type' => '2'));

    $fordQty = count($fords);
    $hondaQty = count($hondas);

}

Some details at: http://www.doctrine-project.org/docs/orm/2.0/en/reference/working-with-objects.html#by-simple-conditions

Answer 2

Using QueryBuilder, try:

$qb->select(" SUM(CASE WHEN t.id = 1 THEN 1 ELSE 0 END) as fords ")
->addSelect(" SUM(CASE WHEN t.id = 2 THEN 1 ELSE 0 END) as hondas ")

Answer 3

Using the query builder:

$query = $respository
        ->createQueryBuilder('u')
        ->select('count(u)')
        ->getQuery()
;

$total = $query->getSingleResult();

:)