CakePHP, Custom Queries, and the Result Array

Note: Apparently this no longer works in Cake 2.x. I’m a little disappointed by that, but that’s the way it is.

Just a quick post about a wonderful trick I stumbled across last night while experimenting with Virtual Fields on models in CakePHP. Since I started using Cake, I’ve been annoyed that when I ran a custom query, the results would come back in the ‘[0]’ index of the result array, because Cake couldn’t tell what tablename they were from. I’d experimented with a couple different ways to alias them to try and get them to work, but none of them worked. I eventually gave up, and just dealt with it.

So, last night while setting up my first virtual field, I looked at the debug log. There, I discovered this query:

SELECT 
    `Package`.`id`, 
    `Package`.`name`, 
    `Package`.`price`, 
    (CONCAT(`Package`.`name`, " ($", `Package`.`price`, ")")) 
        AS `Package__list_name` 
FROM 
    `packages` AS `Package`

The key part to notice is the alias it gave to the CONCAT() field. ‘Package__list_name’. That allows Cake to properly identify the model that the field belongs to when retrieving the results, and it can then put it into the array properly, resulting in this array structure:

array (
  0 => 
  array (
    'Package' => 
    array (
      'id' => '1',
      'name' => 'Basic MU* Hosting',
      'price' => '5.00',
      'list_name' => 'Basic MU* Hosting ($5.00)',
    ),
  ),
  1 => 
  array (
    'Package' => 
    array (
      'id' => '2',
      'name' => 'Basic Web Hosting',
      'price' => '5.00',
      'list_name' => 'Basic Web Hosting ($5.00)',
    ),
  ),
)

I hope this tip is as useful to others as it will be to me. It may not seem like much, but it will be very nice to be able to place items where I want them in the result arrays.

%d bloggers like this: