Mysql Codeigniter Active Record - how do I make a where_in query and return the correct order of results?

Question

I have a set of identifiers passed in a specific order that I need to save. I request from several left connections the data associated with each ID. Identifiers are returned from the search, so the order must be preserved in order to make the results valid (otherwise the search makes it useless).

My code looks something like this:

$this->db->where_in('id', $array_of_ordered_ids); 

For instance -

 $this->db->where_in('id', array(4,5,2,6)); 

Will return the results in the order of 2,4,5,6.

I would like him to keep order and return the results 4,5,2,6.

Thanks,

Answer 1

To order the result in order in your array, you can do the following:

 $array_of_ordered_ids = array(4,5,2,6); 

As you already know the order of the numbers, you can use the Mysql FIELD() ^Docs function:

 ORDER BY FIELD(id, 4, 5, 2, 6); 

To create such a string, you can use implode ^Docs :

 $order = sprintf('FIELD(id, %s)', implode(', ', $array_of_ordered_ids)); 

Try:

 $array_of_ordered_ids = array(4,5,2,6); $this->db->where_in('id', $array_of_ordered_ids); $order = sprintf('FIELD(id, %s)', implode(', ', $array_of_ordered_ids)); $this->db->order_by($order); 

Answer 2

Like all the answers I found on SO, where it was only semi-correct, but everyone gave good hints, I successfully implemented code to extract a set of strings ordered in order in a given array.

To create sql as follows:

 SELECT * FROM (`product`) WHERE `id` IN (2, 34, 234) ORDER BY FIELD(`id`, 2, 34, 234) 

use this code, and $ id contains an array (2, 34, 234).

 // select from ... $this->db->where_in('id',$ids); $this->db->_protect_identifiers = FALSE; // stop CI adding backticks $order = sprintf('FIELD(id, %s)', implode(', ', $ids)); $this->db->order_by($order); $this->db->_protect_identifiers = TRUE; // switch on again for security reasons // get... 

see also here: http://ellislab.com/forums/viewthread/137157/#1058666

Answer 3

Thanks for the biggest solution.

  $orde_num_string = implode(",",$order_num); $this->db->where_in("cir_order.order_num",$order_num); $this->db->_protect_identifiers = FALSE; $this ->db->order_by("FIELD(cir_order.order_num, $orde_num_string)"); $this->db->_protect_identifiers = TRUE;