Doctrine DQL query to Mysql query syntax error

Question

I am having a hard time finding out why this Doctrine dql query is not working in my symfony application.

Mysql query is this:

SELECT 
    (COUNT(CASE WHEN c_email IS NOT NULL THEN 1 END) * 100.00) / COUNT(c_number) AS percentage
FROM 
    distinct_customers;

My Symfony doctrine php code is this

   public function invalidEmails()
    {
        $em = $this->getEntityManager();
        $qb = $em->createQuery('
            SELECT (count(case  when  ds.c_email IS NOT null then 1 end))*100/count(ds.c_number) as percentage FROM App\Entity\DistinctCustomers ds');
        return $qb->getResult();
    }

But I get an error each time

[Syntax Error] line 0, col 69: Error: Expected Doctrine\ORM\Query\Lexer::T_ELSE, got 'end'

has someone ran into this in the past?

Answer 1

Your CASE block needs an ELSE condition.

In addition, it looks like you're trying to count the cases where email is not null, but instead of using the COUNT function (which would count nulls and zeroes as well as 1's) you need to use the SUM function.

Try this:

SELECT(
    SUM(
        CASE
        WHEN ds.c_email IS NOT null
        THEN 1
        ELSE 0
        END
    )
)*100/COUNT(ds.c_number) AS percentage
FROM App\Entity\DistinctCustomers ds