Doctrine Query Builder, CASE WHEN in ORDER BY

Question

I'm trying to make query with ORDER BY like this in Native SQL

ORDER BY CASE `promotion` WHEN 0 THEN `net_price`
   ELSE `promotion_price`
  END DESC, `net_price` DESC

Something like this

$qb->orderBy('CASE `products.promotion` WHEN 0 THEN `products.net_price`
   ELSE `products.promotion_price`
  END', 'DESC');
$qb->orderBy('products.net_price', 'DESC');

Doesn't work, i'm getting error of unexpected CASE.

So I tried this:

$qb->addSelect('CASE products.promotion WHEN 0 THEN products.net_price ELSE products.promotion_price END price');
$qb->orderBy('products.price', 'DESC');

this time i get

'promotion WHEN': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

In both cases I tried to put it in parenthesis as I found few post saying it helped, unfortunately for me it was just changing error message to unexpected ) in first scenario and

Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got ')'

for 2nd one.

The question is: Is it possible to use condition in ORDER BY using doctrine query builder?

Answer 1

The CASE construction is vendor specific and not part of Doctrine 2 by default. However you can have a look into the DoctrineExtensions. You can use the IfElse function for your use case.