Yii: Query builder: expression in order

1
votes

In AC this order works great, but Query builder make quotes wrong, result error

$models = Yii::app()->db->createCommand()
->select('id, user_id, title, created, modified, lang, forum_id, post_id, status, views, replies, attached')
->from('posts')
->where('post_id = 0')
->order('attached AND forum_id = 1 AND created DESC, created DESC')
->limit(11)
->queryAll();

error:

CDbCommand не удалось исполнить SQL-запрос: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'attached AND forum_id = 1 AND created' in 'order clause'. The SQL statement executed was: SELECT id, user_id, title, created, modified, lang, forum_id, post_id, status, views, replies, attached FROM posts WHERE post_id = 0 ORDER BY attached AND forum_id = 1 AND created DESC, replies DESC, created DESC LIMIT 11

how to fix it?

P.S. Sorry for my English.

2
phpyiiexpressionbuilder
'attached AND forum_id = 1 AND created DESC, created DESC' is not a valid order clause. – crafter

2 Answers

2
votes

Your where and order should probably read:

->where('post_id = 0')
->order('CASE WHEN forum_id =1 THEN 1 ELSE 2 END, created DESC')

Reference: How do I return rows with a specific value first?

0
votes

You should take order expression in round brackets to avoid checking on column. This must work

->order('(attached AND forum_id = 1 AND created) DESC, created DESC')