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.

  • http://sanisoft.com/blog/author/tariquesani/ Tarique Sani

    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]);’));

  • http://bachowski.pl MiB

    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

  • teknoid

    @Tarique Sani

    Nice and concise, thanks for sharing.

  • teknoid

    @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?

  • http://www.mysiteonline.org/ Brendon Kozlowski

    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!

  • teknoid

    @Brendon Kozlowski

    No problem, glad it helped.

  • Pingback: Signets remarquables du 29/09/2008 au 02/10/2008 | Cherry on the...()

  • Rick Torzynski

    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

  • teknoid

    @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.

  • Javier

    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;
    }

  • http://teknoid.wordpress.com teknoid

    @Javier

    Nice, thanks for sharing.

  • Pingback: Consulenza-Web.com » Utilizzare operatori sql in CakePHP find()()

  • Pingback: Ouech.net » Blog Archive » Fixing a Models result array, when doing subqueries()

  • Wayne

    Great article. It helped my development :)

  • http://teknoid.wordpress.com teknoid

    @Wayne

    Good to hear. Cheers ;)

  • http://www.ombakmedia.com/ enthooz

    Thank you. Much appreciated!

  • http://teknoid.wordpress.com teknoid

    @enthooz

    No problem.

  • Andru

    Thanks, a lot just what I was looking for. Thanks to BrendonKoz on IRC for pointing me in the right direction.

  • http://teknoid.wordpress.com teknoid

    @Andru

    Team work ;) good to hear it helped.

  • http://www.farbfinal.de ffstefan

    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.

  • http://teknoid.wordpress.com teknoid

    @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 ;)

  • Blob

    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]);
    }
    }

  • http://teknoid.wordpress.com teknoid

    @Blob

    Great, thank you for sharing.

  • emptywalls

    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.

  • http://teknoid.wordpress.com teknoid

    @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.

  • http://www.nezen.net/ Nicholas Zographos

    To me, snippets like this are why CakePHP is great… such an awesome community of developers!

  • http://teknoid.wordpress.com teknoid

    @Nicholas Zographos

    Nice. glad it helped ;)

  • Greg

    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

  • http://codexico.com.br/blog/ codexico

    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]);
    }
    }

  • Pingback: pc.casey.jp » CakePHP SUM,MAX,MINの結果を正しい配列形式にする()

  • Pingback: CakePHP SUM,MAX,MINの結果を正しい配列形式にする | cakephp.casey.jp()

  • Agus Saputra

    Thanks. It’s helped me.

  • Pingback: Re: Question about $virtualFields | deepinphp.com()

  • Arild

    Hi,

    Great tip, really helpful to create more readable code.

    However, in addition to the problem of the original code not working with with more than one calculated field, there is also a problem with using the “count” find function. This is returned as an array like this: $result[0][0][count], here you will get an error when trying to merge data as there is no $result entry with the name of table!

    The following code implements earlier fixed, and additionally checks that there is an entry in the $result array that matches the table name:

    function afterFind($results, $primary = false) {
    if($primary == true) {
    if(Set::check($results, ‘0.0’) && Set::check($results, “0.” . $this->alias)) {
    foreach($results as &$entry) {
    $entry[$this->alias] = array_merge($entry[$this->alias], $entry[0]);
    unset($entry[0]);
    }
    return $results;
    }
    }
    }

  • teknoid

    @Arild

    Thank you for sharing.

  • Pingback: CakePHPでGROUP BYしたときのCOUNTやSUMの取得結果を変更する - Beyond prep()

  • http://non Carlos

    Instead of afterFind, you can use virtualfields so this sum() function() fields are put at the same level as concrete fields. Check: http://book.cakephp.org/2.0/en/models/virtual-fields.html

    See section: Virtual fields in SQL queries

  • teknoid

    @Carlos

    I haven’t tried this with the find() methods of cake. Wonder if that would work in the same fashion.

    Also older versions of cake didn’t have the luxury of virtual fields ;)