Order by field

Yesterday, Tarique Sani mentioned in a thread in the CakePHP Group “order by field”. I know “order by”, but I never heard about “order by field” (which seems to be MySQL-specific).

Ok, let’s do some examples to learn more about it. We will use the following countries table:

[1] => USA
[2] => Germany
[3] => Russia
[4] => Austria
[5] => China
[6] => Switzerland

Now, we don’t want to sort them by the id, but by some “strange” order: Austria, USA, China, Russia, Switzerland, Germany. For this purpose we can use “order by field”: the first parameter is the column name and all following parameters are values of the respective column. In CakePHP it is done in the following way:

$this->Country->find('list', array('order' => array('FIELD(Country.id, 4, 1, 5, 3, 6, 2)')));

And we get the expected result:

[4] => Austria
[1] => USA
[5] => China
[3] => Russia
[6] => Switzerland
[2] => Germany

Probably more common is the scenario that you want to have certain values at the top of a list, and the other values of the list should be ordered alphabetically. If we want the German-speaking countries at the top of the list we would do:

$this->Country->find('list', array('order' => array('FIELD(Country.id, 2, 4, 6)', 'Country.name')));

or 

$this->Country->find('list', array('order' => array('FIELD(Country.name, "Germany", "Austria", "Switzerland")', 'Country.name')));

Unfortunately, this doesn’t return the expected result, the records which should be at the top are at the bottom:

[5] => China
[3] => Russia
[1] => USA
[2] => Germany
[4] => Austria
[6] => Switzerland

We can solve this “problem” by using the “DESC” keyword and changing the order of the provided values:

$this->Country->find('list', array('order' => array('FIELD(Country.id, 6, 4, 2) DESC', 'Country.name')));

or

$this->Country->find('list', array('order' => array('FIELD(Country.name, "Switzerland", "Austria", "Germany") DESC', 'Country.name')))

With those changes we get the expected result:

[2] => Germany
[4] => Austria
[6] => Switzerland
[5] => China
[3] => Russia
[1] => USA

Thanks to Tarique for mentioning “order by field” and to Abhimanyu Grover for asking the original question.