CODEWITHSUNDEEP
phpsymfonydoctrinedoctrine-query
user12438439
user12438439

Reputation:

Doctrine DQL query to Mysql query syntax error

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?

Upvotes: 1

Views: 240

Answers (1)

Harry Mustoe-Playfair
Harry Mustoe-Playfair

Reputation: 1429

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

Upvotes: 1

Related Questions