Let’s say we’ve got some calculated field in our find() method, something like SUM() or COUNT() or maybe AVG().
For the sake of example let’s do something simplistic, like:
pr($this->Company->find('all', array('fields'=>array('Company.id','Company.name', 'COUNT(id) AS total_count'), 'group'=>array('Company.name'), 'recursive'=>-1)));
This is all fine and well, except our total_count field does not appear with the rest of the fields in the resulting data array, but rather in it’s own index, like here:
Array ( [0] => Array ( [Company] => Array ( [id] => 1 [name] => New Company ) [0] => Array ( [total_count] => 1 ) )
We can use Set::check() and easily reformat our array in afterFind(), so that we can reference all fields in a consistent manner.
Try something like this in your model (or app model):
function afterFind($results, $primary=false) { if($primary == true) { if(Set::check($results, '0.0')) { $fieldName = key($results[0][0]); foreach($results as $key=>$value) { $results[$key][$this->alias][$fieldName] = $value[0][$fieldName]; unset($results[$key][0]); } } } return $results; }
Now all fields appear where we would expect them.
Referencing all fields in a consistent manner (in the view, for example) is already a good enough benefit to use an approach similar to this one, but in addition if the change is ever made to the core in order to fix this up, your code will not be affected in any way.
Try array_walk and lambda functions if you want abbreviated code I recently wrote
array_walk($results, create_function(‘&$v’, ‘$v[“Photo”][“rownum”] = $v[0][“rownum”]; unset($v[0]);’));
With postgres DBO (maybe in some other too) you don`t have to do such things. You just type:
COUNT(id) AS “Company__total_count”
Regards
@Tarique Sani
Nice and concise, thanks for sharing.
@MiB
So are you saying that ‘Company__total_count’ field is going to be on the same index level as the other fields in the results?
Ah, a perfect example of using Set rather than some other method to keep it compatible in future versions. I also should have realized that placing this in afterFind would have been better than where I placed my own version. Thanks teknoid!
@Brendon Kozlowski
No problem, glad it helped.
I’m trying to use the SUM function, but it just doesn’t seem to insert that into the query produced by find:
$query = $this->find(‘all’,array(
‘field’ => array(
‘Activity.project_id’,
‘Activity.task_id’,
‘Activity.activity_id’,
‘SUM(Activity.worktime) AS tasktime’),
‘condition’ => array (
‘Activity.project_id’=>$project_id,
‘Activity.task_id’=>$task_id),
‘group’ => array(
‘Activity.project_id,
Activity.task_id,
Activity.tclock_id’)
)
);
// Used this to make it accessible
return $this->afterFind($query,true);
But this doesn’t generate the query I expected:
SELECT `Activity`.`id`, `Activity`.`project_id`, `Activity`.`task_id`, `Activity`.`tclock_id`, `Activity`.`startstamp`, `Activity`.`stopstamp`, `Activity`.`worktime`, `Task`.`id`, `Task`.`project_id`, `Task`.`name`, `Task`.`description`, `Task`.`status`, `Task`.`category`, `Note`.`id`, `Note`.`activity_id`, `Note`.`note` FROM `activities` AS `Activity` LEFT JOIN `tasks` AS `Task` ON (`Activity`.`task_id` = `Task`.`id`) LEFT JOIN `notes` AS `Note` ON (`Note`.`activity_id` = `Activity`.`id`) WHERE 1 = 1 GROUP BY `Activity`.`project_id`,`Activity`.`task_id`,`Activity`.`tclock_id`
If instead I use a custom query (in the model…) like so:
$query = “SELECT project_id,task_id,tclock_id, SUM(worktime) AS tasktime
FROM activities
WHERE project_id=$project_id AND task_id=$task_id
GROUP BY project_id,task_id,tclock_id”;
return $this->afterFind($this->query($query),true);
Then this does return the sum as the variable expected. Can I do this query through find() instead of resorting to a custom query? And is my calling of afterFind correct?
Rick
@Rick Torzynski
Well, for one I see you have ‘field’ instead of ‘fields’… but please post this question at the google group. You’ll get better/faster answers there.
This code didn’t work for me in a query with two aggregated fields. I’ve modified it a bit:
function afterFind($results, $primary=false) {
if($primary == true) {
if(Set::check($results, ‘0.0’)) {
$fields = array_keys( $results[0][0] );
foreach($results as $key=>$value) {
foreach( $fields as $fieldName ) {
$results[$key][$this->alias][$fieldName] = $value[0][$fieldName];
}
unset($results[$key][0]);
}
}
}
return $results;
}
@Javier
Nice, thanks for sharing.
Great article. It helped my development :)
@Wayne
Good to hear. Cheers ;)
Thank you. Much appreciated!
@enthooz
No problem.
Thanks, a lot just what I was looking for. Thanks to BrendonKoz on IRC for pointing me in the right direction.
@Andru
Team work ;) good to hear it helped.
nice to see a workarround – thanks! I hope that gets fixed with a native mapping ability within the find() function, cause doing another for…each seems a lot overheaded. I think using native SQL functions in queries _must_ be possible.
@ffstefan
It is possible, just a little awkward. I wouldn’t worry about overhead too much… squeezing out extra milliseconds from your app is pointless. Hardware is cheap, man-hours are not ;)
Great solution teknoid, Thanks a lot.
Noted that this code won`t work if there are more then one calculated fields.
Modified it like this for myself:
if (Set::check($results, ‘0.0’)) {
foreach($results as &$entry) {
$entry[$this->alias] = array_merge($entry[$this->alias], $entry[0]);
unset($entry[0]);
}
}
@Blob
Great, thank you for sharing.
Thank you for this function! CakePHP’s documentation is so lacking, its like a treasure hunt to figure things out. Luckily I found this gem.
@emptywalls
I’m glad it was helpful…
But I’d have to say that trying to cover every detail, such as this one, in the manual would be impossible.
To me, snippets like this are why CakePHP is great… such an awesome community of developers!
@Nicholas Zographos
Nice. glad it helped ;)
Nice piece of code !
Just a quick note: if you are using this callback inside of a behavior, you have to change $this->alias $model->alias (in: $results[$key][$model->alias][$fieldName] = $value[0][$fieldName];)
Assuming of course that the functions looks like: function afterFind(&$model, $results, $primary=false)
Greg
Thank you all!
@Blob
Sometimes find returns empty:
if (Set::check($results, ‘0.0’)) {
foreach($results as &$entry) {
$entry[$this->alias] = isset($entry[$this->alias]) ? array_merge($entry[$this->alias], $entry[0]) : $entry[0];
unset($entry[0]);
}
}
Thanks for the code it really helped although it only worked for me if the query has only one calculated field. I made some changes to make it work for multiple fields. Here is the changed code:
function afterFind($results, $primary = false) {
if ($primary == true) {
if (Set::check($results, ‘0.0’)) {
$fieldNames = array_keys($results[0][0]);
if (!empty($fieldNames)) {
foreach ($results as $key => $value) {
foreach ($fieldNames as $fieldName) {
$results[$key][$this->alias][$fieldName] = $value[0][$fieldName];
unset($results[$key][0]);
}
}
}
}
}
return $results;
}
Thanks.