Sunday, October 5, 2008

Formatting the result returned from a custom query in cakephp.

When you do custom queries with cakephp, the data is returned in mysql's format

For example

$this->User->query('Select * from cars as Car');

array(
[0] => array('Car' => array('name' => 'ford',
        'colour' => 'yellow')),
[1] => array('Car' => array('name' => 'toyota',
        'colour' => 'green')),
[2] => array('Car' => array('name' => 'nissan',
        'colour' => 'pink')))

What we want though is a more cakephp result set that looks like what we would get from a ->find('all') query:
eg.
array(
array('Car' => array(0 => array('name' => 'ford',
        'colour' => 'yellow'),
1 => array('name' => 'toyota',
        'colour' => 'green'),
2 => array('name' => 'nissan',
        'colour' => 'pink')) );


Solution:
Use the set extract function and assign it to a string

array('Car' => Set::extract($this->User->query($sql), '{n}.Car'))