We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

How to use Order By CASE WHEN THEN ... in PHQL

I have a sql like this:

SELECT * FROM poem WHERE title LIKE '%John%' ORDER BY CASE WHEN title = 'John' THEN 0 WHEN title LIKE 'John%' THEN 1 WHEN title LIKE '%John%' THEN 2 ELSE 3 END, author ASC LIMIT 10;

I tried this:

$order = "CASE 
               WHEN title = '{$keyword}' THEN 0 
               WHEN title like '{$keyword}%' THEN 1 
               WHEN title like '%{$keyword}%' THEN 2 
               ELSE 3 
               END, author ASC";
$resultset = $this->modelsManager->createBuilder()
           ->from('Poem')
           ->limit(10)
           ->orderBy($order)
           ->getQuery()
           ->execute();

and

$poems = \Modules\Frontend\Models\Poem::find(array(
    'columns' => 'id, title, author, period, content, genre',
    'conditions' => $where, 
    'order' => $order, 
    'limit' => array('number' => $perPage, 'offset' => $offset),
));

Both give me the same error:

Syntax error, unexpected token IDENTIFIER(WHEN), near to ' title = 'John' THEN 0 WHEN title ......

It seems when parsePHQL, Phalcon can not recognize "WHEN".

I know in document there says:

A database system could offer specific SQL extensions that aren’t supported by PHQL, in this case, a raw SQL can be appropriate:
But case...when should be a standard sql statement,

My question is , can I use "case when" statement without using raw sql in Phalcon?



98.9k
Accepted
answer

CASE clauses are not supported by PHQL. You can use either a Db\Rawvalue or use a raw SQL statement like explained here: https://docs.phalcon.io/es/latest/reference/phql.html#using-raw-sql

Or replace the CASE clause by IF:

$order = "IF (title = '{$keyword}', 0, 
IF (title like '{$keyword}%', 1
IF (title like '{$keyword}%', 2)))
, author ASC";

I changed $order argument to

$order = "IF (title = '{$keyword}', 0, 
              IF (title like '{$keyword}%', 1,
                  IF (title like '{$keyword}%', 2, 3)))
          , author ASC";

And it works like magic! Thanks!