Doctrine CASE WHEN with WHERE IN statement

Question

Good day, everyone!

I have following Doctrine query to retrieve some CTI entities:

SELECT s.id 
FROM CRM\SpendBundle\Entity\Spend s
WHERE (CASE 
        WHEN s INSTANCE OF 'CRM\BusinessTripBundle\Entity\BusinessTrip' THEN 'Business Trip' 
        WHEN s INSTANCE OF 'CRM\ExpenseRequestBundle\Entity\ExpenseRequest' THEN 'Expense Request' 
        ELSE '' END) IN(:availableSpends)

Parameter availableSpends is just an array of strings:

availableSpends = array('Expense Request')

And this query throws an error:

[Syntax Error] line 0, col 439: Error: Expected =, <, <=, <>, >, >=, !=, got 'IN'

What can i do with CASE statement to avoid this error? Doctrine version is 2.5.1

Have you tried, just for testing purpose, with querybuilder ? `( $qb ->where('c INSTANCE OF :param') ->setParameter('param', $em->getClassMetadata('My\Entity\Class'))` and `$qb->expr()->in('u.id', array(1, 2, 3))` — Delphine, Sep 14 '16 at 07:57
Or.. Add "CASE result" in your "SELECT" with an alias and try to apply your "WHERE filter" on this alias. Don't sure if it's gonna work, I can't test — Delphine, Sep 14 '16 at 08:14

score 0 · Answer 1 · answered Sep 14 '16 at 10:21

You can rewrite the statement in case it did not work with you as something like the following:

SELECT s.id 
FROM CRM\SpendBundle\Entity\Spend s
WHERE (s INSTANCE OF 'CRM\BusinessTripBundle\Entity\BusinessTrip' and 'Business Trip' IN (:availableSpends))
   or (s INSTANCE OF 'CRM\ExpenseRequestBundle\Entity\ExpenseRequest' and 'Expense Request' IN (:availableSpends))

Doctrine CASE WHEN with WHERE IN statement

1 Answers1