Dealing with calculated fields in CakePHP’s find()

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.

---

Possibly related posts: (automatically generated)

• Autofield behavior or how to add additional columns to your models.
• Find Function