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?

@Myonara Hi, I did try that but it skips over the first case and returns this array(1) { [0]=> array(1) { ["percentage"]=> string(8) "100.0000" } } — user12438439
– user12438439, Commented Nov 26, 2019 at 13:49

Harry Mustoe-Playfair · Accepted Answer · 2019-11-26 13:57:54Z

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

answered Nov 26, 2019 at 13:57

Harry Mustoe-Playfair

1,50017 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Doctrine DQL query to Mysql query syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related