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?