如何订购查询

Question

I have an array $sorted_array its value is

Array ( [0] => 3 [1] => 1 [2] => 6 )

Now based on the $sorted_array i created an array

$first_array = Yii::app()->db->createCommand()
            ->select('*')
            ->from('form_fields')                                      
             ->where(array('not in', 'id', $sorted_array))
             ->andWhere('form_id=:form_id', array(':form_id'=>$form_id))
             ->queryAll();

$sorted_array value is the id (Primary key) of table form_fields.

When i run this query i get the array $first_array but not in the order in which i want it. ie, I will get an array in order $id=1,3,6.

Now my wanted order is 3,1,6 (exactly as $sorted_array). How can i get $first_array in that order?

Answer 1

You can do using ->order() Add order( 'id' ) to your code this way:

 $first_array = Yii::app()->db->createCommand()
        ->select('*')
        ->from('form_fields')                                      
         ->where(array('not in', 'id', $sorted_array))
         ->andWhere('form_id=:form_id', array(':form_id'=>$form_id))
         ->order('id')
         ->queryAll();

otherwise if is not possibile build the query you need with query builder you can use

Yii::app()->db->createCommand("select * from your_table")->queryAll();

and for binding param

Yii::app()->db->createCommand(
        'select * from your_table  where yuor_field =:your_param')->
      bindValue('your_param',$yuor_value)->queryAll();

this returns all rows using a specified SQL statement

$sql = "select * from form_fields 
       where id not in (3,1,6) 
       and form_id = " . $form_id .
       "order by field(id, 3,6,1);";

$first_array = Yii::app()->db->createCommand($sql)->queryAll();