Doctrine 2: Counting Elements by Type Select

Question

Does anyone know how to get a score based on a condition in a select () statement of a Doctrine 2 QueryBuilder?

Here is what I have tried so far ...

My first attempt was to try count () with eq (). The error I get says: "The expected parentheses received are equal."

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

Then I tried count () with have (). The received error says: "The maximum nesting level of the function has been reached."

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

Then I tried count () with where () and eq (). Again I got the "Maximum Nesting Function".

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

Then I tried these options using in (). They both give the syntax error 'Expected OT received' ('

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

In the in () examples, I also tried passing the value as a variable and through 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

Unless you need to stay with DQL, this can 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: http://www.doctrine-project.org/docs/orm/2.0/en/reference/working-with-objects.html#by-simple-conditions

Answer 2

Using the query builder:

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

:)

Answer 3

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 ")