Cake PHP Cake PHP - 5 months ago 31
PHP Question

Custom Order By in CakePHP

I have a problem in CakePHP

I have an array ($A) and its value

Array // in $A
(
[0] => 3
[1] => 4
...
}


inside the controller:

$conditions[]=array('Room.place_id' =>$A,'Room.status'=>'1');

$this->paginate= array(

'limit' =>50,

'recursive' => 2,

'conditions' => $conditions,

);


now result is :

Array
(
[0] => Array
(
[Room] => Array
(
[id] => 1
[place_id] => 1
[type] => place
)
)
[1] => Array
(
[Room] => Array
(
[id] => 2
[place_id] => 3
)
).
...


Now i want to order by (in Place id) using $A value ...

I want the first record to be the Room with [place_id] => 3 (because $a[0] = 3)

The second record should be the Room with [place_id] => 4 (because $a[1] = 4)
and so on

ori ori
Answer

In mysql you can order by specific field values, by using ORDER BY FIELD:

SELECT * FROM rooms WHERE place_id IN (3,1) ORDER BY FIELD(place_id, 3, 1);

This will order your rooms in the custom order you wanted (first those with place_id=3 then those with place_id=1).

To do it in cake:

$this->paginate= array(
   'order' => 'FIELD(Room.place_id, '.implode(',', $A).')',
   ...

I'm assuming that $A contains only numbers, otherwise you'll have to sanitize the values that go into the FIELD expression.

Comments