Fixing a Models result array, when doing subqueries
In some cases, you want to grab extra data in the find() method by calling an SQL statement like COUNT() AS, or SELECT(). When you do this, your extra data is not nested in the Model index of your resulted array. In the example below, we are doing a test find() and taking a look at the returned array.
// Find() query
$this->User->find('all', array(
'fields' => array(
'User.*',
'COUNT(User.id) AS totalUsers'
)
));
/* Resulting array
[User] => Array (
[id] => 1
[username] => milesj
)
[0] => Array (
[totalUsers] => 100
)*/Now there are two ways to fix this problem, one is doing it in the afterFind() of your model, and the other is editing the core CakePHP DBO files. The first method can be found at the link below, and was written by a fellow baker, Teknoid. This technique would only apply to the model it was put in, the next technique applies it globally.
http://teknoid.wordpress.com/2008/09/29/dealing-with-calculated-fields-in-cakephps-find/
The second method is editing the resultSet() method of your datasource (does not apply to all datasources), which was brought to my attention by grigri. In my example, this technique will work for both MySQL and MySQLi, but I will be using MySQLi. Ee need to open the MySQLi datasource found at cake/libs/model/datasources/dbo/dbo_mysqli.php, copy the whole code and save our own version at app/model/datasources/dbo/dbo_mysqli.php. Once we have created our own file, we will navigate our way down to the method resultSet(). All we need to do is add another if statement in the while loop that looks for a result similar to Model__fieldName. Below you can see the before and after edits (only a part of the method):
// Old code block
while ($j < $numFields) {
$column = mysqli_fetch_field_direct($results, $j);
if (!empty($column->table)) {
$this->map[$index++] = array($column->table, $column->name);
} else {
$this->map[$index++] = array(0, $column->name);
}
$j++;
}
// New code block
while ($j < $numFields) {
$column = mysqli_fetch_field_direct($results,$j);
if (!empty($column->table)) {
$this->map[$index++] = array($column->table, $column->name);
} else {
if (strpos($column->name, '__')) {
$parts = explode('__', $column->name);
$this->map[$index++] = array($parts[0], $parts[1]);
} else {
$this->map[$index++] = array(0, $column->name);
}
}
$j++;
}This technique is probably the easiest to do, and will apply to all models. Once we have altered our datasource, we can change our find() method and our result should be working correctly now.
// Find() query
$this->User->find('all', array(
'fields' => array(
'User.*',
'COUNT(User.id) AS User__totalUsers'
)
));
/* Resulting array
[User] => Array (
[id] => 1
[username] => milesj
[totalUsers] => 100
)*/