CakePHP – Sort Find() results by Array of Ids? – Order by Array – MYSQL

This is one of the question I was faced in my recent project. I would like to share the same with you.

It was raised when I have to order paginate results in an order of set of IDs. I was trying to recall many scenarios. But no use.

Then I was started thinking, do MySQL have any similar option, Yes, MySQL have it’s FIELD(). It was really awesome one and which help me furnish my needs well.

This scenario will come across whenever you were force to get results from external sources.


SELECT ID, NAME
FROM PROJECTS
WHERE ID IN (10,3,5);

But here you will results in an order 3,5,10. But actually we need them in the same order of IN. ie. 10,3,5

Now fields come to game

SELECT ID, NAME
 FROM PROJECTS
 WHERE ID IN (10,3,5)
 ORDER BY FIELD(id,10,3,5);

Will give you the results are you looking

Make sure that you are not using any ASC / DESC for exactly the same result in order of id mentioned.

Here make sure that you are NOT doing following mistakes

SELECT ID, NAME
FROM PROJECTS
WHERE ID IN (10,3,5)
ORDER BY FIELD('id',10,3,5);
/* id in quotes so you will never get any error
 and No intended results too */

 

SELECT ID, NAME
FROM PROJECTS
WHERE ID IN (10,3,5)
ORDER BY FIELD(id,'10,3,5');
/* Now I am keeping all the array values as string,
 which will again give wrong results. */

Cake PHP Version

Now how can we convert the same to CakePHP? Though it’s look a tedious task, it quite simpler game.

Assume that


$id_array =  array(10,3,5);

// Creating String order. Here we cannot use
// array order as this little complex
$order = "FIELD(id,". implode(“, “, $id_array).")";
// See I have not mentioned ant ASC or DESC

// Now you can assign the same to find as below,
$projects = $this->Project->find('all', array(
	'conditions' => array(
		'Coupon.id' => $id_array ,
	),
	'order' => $order, // See here
	'recursive' => -1
));

// Print array and see the magic!!
pr($projects);

Even you can same for all find methods including find(“first”), find(“list”) and paginate..

Thanks

Subin George
  • Sion Fletcher

    Thanks, was doing all sorts of complicated array sorting afterwards until I found this.