Cphalcon: [2.0.10] Order By Case When - produces an error

[hailie-rei]

I use Phalcon 2.0.10, MySQL.

I create db queries using query builder:

$query->orderBy('CASE m.meaning WHEN m.meaning = ' . $safeMeaning . ' THEN 0 WHEN m.meaning LIKE ' . $safeMeaningLike . ' THEN 1 ELSE 2 END ASC, m.meaning ');

This code works without errors, but returns wrong result:

ORDER BY CASE m.meaning WHEN m.meaning = 'value' THEN 0 WHEN m.meaning LIKE 'value%' THEN 1 ELSE 2 END ASC, m.meaning

I have to use this variant:

ORDER BY CASE WHEN m.meaning = 'value' THEN 0 WHEN m.meaning LIKE 'value%' THEN 1 ELSE 2 END ASC, m.meaning

But I receive an error: PhalconException: Syntax error, unexpected token WHEN, near to ' m.meaning = 'в' THEN 0 WHEN m.meaning LIKE 'в%' THEN 1 ELSE 2 END ASC, m.meaning LIMIT :APL0:', when parsing ...

I wrote the second query directly to the DB and it returned the results I needed.

Also I tried

IF (m.meaning = 'value', 0, IF (m.meaning LIKE 'value%', 1, 2) ) ASC, m.meaning

and it works.

[Jurigag]

Well to be honest phql case have only this first syntax i think. Like CASE what WHEN value THEN expression

I think currently there is no this syntax:

CASE WHEN condition THEN expression

[stamster]

That query is too specific to a certain RDBMS (MySQL) in your case.

ORM will just not handle it well at the syntax level.

You can use raw query / PDO in Phalcon, that's your best shot IMHO.

Refer to: https://docs.phalconphp.com/en/latest/api/Phalcon_Db_Adapter_Pdo_Mysql.html

[sergeyklay]

I agree with @stamster