1

I've written the following code to build the query using query builder.

            $em = $this->_em;
            $qb = $em->createQueryBuilder();
            $qb->select('tbl');
            $qb->addSelect('COUNT(tbl.macId) AS totalInstallations');
            $qb->addSelect('COUNT(DISTINCT tbl.macId) AS uniqueInstallations');
            $qb->addSelect('COUNT(CASE  
                             WHEN tbl.updatedOn IS NOT NULL THEN tbl.macId ELSE NULL
                            END) AS totalUninstallations');
            $qb->from('Entity\SoftwareInstallation', 'tbl');
            $result = $qb->getQuery()->getArrayResult();
            return $result;

But it's not working in the case condition.

I get the below error:

Type: Doctrine\ORM\Query\QueryException

Message: [Syntax Error] line 0, col 152: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got '.'

Filename: /var/www/html/ghostnew/application/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php

Line Number: 52

halfer
  • 19,824
  • 17
  • 99
  • 186
Manish Jangir
  • 5,329
  • 4
  • 42
  • 75

1 Answers1

-2

Try using SUM() instead of COUNT().

See this answer: Doctrine 2 DQL CASE WHEN in Count

Community
  • 1
  • 1
DinoAmino
  • 93
  • 6
  • Though replacing COUNT with SUM works in your case there seems to be a general problem with using CASE parts in DQL. I opened an issue for it here: https://github.com/doctrine/doctrine2/issues/5915 Feel free to subscribe. – webDEVILopers Jul 03 '16 at 07:45