Anthony Anthony - 5 months ago 41
PHP Question

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

I have a set of IDs passed in a particular order which needs to be retained.
I am querying from several left joins the data related to each ID.
The IDs are returned from a search so the order must be kept to make the results valid (otherwise it makes searching rather pointless).

My code looks something like;

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


For example -

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


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

I'd like for it to retain the order and return the results 4,5,2,6.

Thank you,

Answer

To order the result by the 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 implodeDocs:

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

Give it a 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);