2

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.

lin
  • 17,956
  • 4
  • 59
  • 83
You Care
  • 488
  • 4
  • 20

2 Answers2

3

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?

Community
  • 1
  • 1
topher
  • 14,790
  • 7
  • 54
  • 70
  • I don't need get only post where forum_id = 1, it's just must be first then another posts – You Care Aug 12 '14 at 19:41
  • Then why don't you order by `id` first? – topher Aug 12 '14 at 20:11
  • Look: attached AND forum_id = 1 AND created DESC, created DESC 1) "attached AND forun_id = 1 AND created DESC".. means i need first posts which attached and in forum section with id 1 (News & Annoces..) and this posts must be order by "created DESC" (new in top). 2) ..",created DESC" another posts must sort only be created time. i don't want to sort by id – You Care Aug 12 '14 at 20:35
  • As your error states, the error is being caused by the text `ORDER BY attached AND forum_id = 1 AND created DESC, replies DESC, created DESC LIMIT 11`. I've updated the answer with an SQL standard way of specifying your order. – topher Aug 12 '14 at 21:14
  • and again no. my query works fine in AC Find or if remove filter in CDbCommand in ORDER method: https://github.com/yiisoft/yii/blob/1.1.15/framework/db/CDbCommand.php#L1066 e.g.: public function order($columns) { $this->_query['order']=$columns; return $this; } Just i don't want to change yii files, but this method automatically insert quote whats destroy string. – You Care Aug 12 '14 at 22:45
  • What is AC? And what is the exact query you are trying to run? – topher Aug 13 '14 at 03:39
  • AC - CActiveRecord, query look at topic – You Care Aug 13 '14 at 13:26
0

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')
Alexey Muravyov
  • 1,106
  • 13
  • 17