CODEWITHSUNDEEP
cakephpgroup-bysum
Carlos Garcia
Carlos Garcia

Reputation: 359

SUM field from contained records

On cake 2.1, I need to SUM a field from the contained records.

Currently I get the sum of All records on the child table, but need to group by the id of my main table (employee).

Attend belongs to Employee

    $agents = $this->Employee->find('all', array(
        'fields' => array('Employee.FullNameNoId'),
        'conditions' => $conditions,
        'contain' => array(
            'Attend' => array(
                'conditions' => array(
                    'Attend.as_date BETWEEN ? AND ?' => array(
                        date('Y-m-d', strtotime($this->passedArgs['date1'])), 
                        date('Y-m-d', strtotime($this->passedArgs['date2']))
                    )
                ),
                'fields' => array('sum(Attend.as_labormin) AS total'),
                //'group' => array('Attend.employee_id') // This gets sql errors below
            )
        )
     ));

Tried several combinations with SQL errors:

'group' => array('Attend.employee.id') // Model "Attend" is not associated with model "Attend" 
                                      //Column not found: 1054 Unknown column                     
'group' => array('employee_id')        // Model "Attend" is not associated with model "employee_id"
                                      // Column not found: 1054 Unknown column 'Attend.group' in 'field list'

'group' => array('Employee.id')           //Column not found: 1054 Unknown column Attend.group' in 'field list'

Relation betweent tables is fine, I can get related records, problem is to get a sum by employee id.

Checked Cakephp SUM of related field, but it seems cumbersome to use SELECT SUM, and they left out the grouping needed.

Can you help?

Upvotes: 1

Views: 1946

Answers (1)

Lawrence Barsanti
Lawrence Barsanti

Reputation: 33352

Try disabling 'autoFields'. It is known to cause SQL errors with aggregate functions and 'group by' statements. Find out more here

Upvotes: 1

Related Questions