How to Order a query

Yii 1.1 General Discussion
1

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?

2

You can Try making a temporary table with, values as in $sorted_array, then apply sort on this table, joining with original one.

3

hi,

i tried something like this




$SQL="SELECT * FROM form_fields WHERE id NOT IN {$sorted_array}  ORDER BY FIELD (id, {$sorted_array})";

$connection=Yii::app()->db; 

$command=$connection->createCommand($SQL);

$rowCount=$command->execute(); 

$dataReader=$command->query();

but it gave me an error [font=Verdana][size=4]Array to string conversion[/size][/font]

4

Hi, Firstly you require Order 3-1-6 Right ??.. And you are rejecting same in condition { NOT IN ($sorted_array) }. This way you are not going right. And order applies on a column, for this here i have a idea,…

    $sorted_array=Array (3,1,6);


    try{


	    $transaction=Yii::app()->db->beginTransaction();


	    $connection=Yii::app()->db;


	    $response=$connection->createCommand()


				->createTable("temp",array("id"=>"INT(11)","order"=>"INT(11)"));


	    


	    foreach($sorted_array as $key=>$value){





		$result=$connection->createCommand()


						 ->insert("temp",array("id"=>$value,"order"=>$key));





		if($result!=1){


			throw new CDbException("Temp Table Insertion Fail");


		}


	    }


	    $first_array = $connection->createCommand()


							    ->select('*')


							    ->from('form_fields as b')


							    ->leftJoin("temp as t","b.id=t.id")


							    ->where(array('in', 'b.id', $sorted_array))


							    ->order("order")


							    ->queryAll();


	    $connection->createCommand()


				->dropTable("temp");


	    $transaction->commit();			


	  if(isset($first_array)){


		print_r($first_array);


	  }


    }


    catch(Exception $e){


	    $transaction->rollback();


	    echo $e->getMessage();


    }