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.

About these ads

31 Responses to Dealing with calculated fields in CakePHP’s find()

  1. Tarique Sani says:

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

  2. MiB says:

    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

  3. teknoid says:

    @Tarique Sani

    Nice and concise, thanks for sharing.

  4. teknoid says:

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

  5. 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!

  6. teknoid says:

    @Brendon Kozlowski

    No problem, glad it helped.

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

  8. Rick Torzynski says:

    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

  9. teknoid says:

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

  10. Javier says:

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

  11. teknoid says:

    @Javier

    Nice, thanks for sharing.

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

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

  14. Wayne says:

    Great article. It helped my development :)

  15. teknoid says:

    @Wayne

    Good to hear. Cheers ;)

  16. enthooz says:

    Thank you. Much appreciated!

  17. teknoid says:

    @enthooz

    No problem.

  18. Andru says:

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

  19. teknoid says:

    @Andru

    Team work ;) good to hear it helped.

  20. ffstefan says:

    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.

  21. teknoid says:

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

  22. Blob says:

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

  23. teknoid says:

    @Blob

    Great, thank you for sharing.

  24. emptywalls says:

    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.

  25. teknoid says:

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

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

  27. teknoid says:

    @Nicholas Zographos

    Nice. glad it helped ;)

  28. Greg says:

    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

  29. codexico says:

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

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 25 other followers

%d bloggers like this: