CakePhp, getting total of many rows in a Containable.

Question

Hi,

I have a query using the Containable behaviors in cakephp thats looks like this :

    $x = $this->find('first',array('contain'=>array(
    'User' => array(
        'SelectionsTeam' => array('conditions' => $conditionTeamSelection,
            'Team' => array(
                        'fields' => array('name','city','id'),
                            'TeamsStat' => $condition,
                                            ),
            'PointsHistory' => array('fields' => array('points'))
                                    ),

    ),
   'conditions' => array('id'=>$id)
));

Everythings work well but the PointsHistory table return me, for example 10 rows with 1 field (points). How can I do to get the SUM of each of thoses 10 rows of points directly in the containable. Because now, I need to do a foreach and make the sum of every PointsHistory row, which I think it's avoidable.

In final, I would like to have the table looks like this : [PointsHistory] => [Total] => 10

instead of :

[PointsHistory] => 
             [0] => 
                    ['points']
                           => 2 
             [1] => 
                    ['points'] 
                           => 1 
             [2] => 
                    ['points'] 
                           => 3

etc.

Thanks a lot for your help!!

Answer 1

I don't think it's possible to get exactly

[PointsHistory] => [Total] => 10

but I believe that what you are looking for is:

    $x = $this->find('first',array('contain'=>array(
    'User' => array(
        'SelectionsTeam' => array('conditions' => $conditionTeamSelection,
            'Team' => array(
                        'fields' => array('name','city','id'),
                            'TeamsStat' => $condition,
                                            ),
            'PointsHistory' => array('fields' => array('SUM(points) total_points'))
                                    ),

    ),
   'conditions' => array('id'=>$id)
));

The result will be something like:

[PointsHistory] => [0] => [Total] => 10

I haven't tested, so change the debug level to 2 and see what code it generate :)