sum() function in cakephp query

Question

I am using this query, but it is not returning ctotal. Please help.

$total = $this->RequestedItem->find('all',
    [
        'sum(cost * quantity) AS ctotal', 
        'conditions' => [
            'RequestedItem.purchase_request_id' => $_GET['po_id']
         ]
     ]
);

Schram

You probably want to avoid putting $_GET['po_id'] into a query directly, even if CakePHP is scrubbing the user input for you. – Lillie

@Lillie if CakePHP already handles injection, what is the harm? – Grammer

Answer 1

You should not be using PHP superglobals directly in CakePHP. You should instead use Model.field naming so that you do not get ambiguous field errors.

Virtual fields is the way to go but that is not your problem, you need to read the book some more.

$total = $this->RequestedItem->find('all', array(array('fields' => array('sum(Model.cost * Model.quantity)   AS ctotal'), 'conditions'=>array('RequestedItem.purchase_request_id'=>$this->params['named']['po_id'])));

should work fine, with the virtualFields it would be

var $virtualFields = array('total' => 'SUM(Model.cost * Model.quantity)');
$total = $this->RequestedItem->find('all', array(array('fields' => array('total'), 'conditions'=>array('RequestedItem.purchase_request_id'=>$this->params['named']['po_id'])));

Fields go in the 'fields' key, just like conditions go in the 'conditions' key. See http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#find

Bellyband

Answer 2

This works too, worked fine for me

    $sum = $this->Modelname->find('all', array(
    'conditions' => array(
    'Modelname.fieldname' => $conditions),
    'fields' => array('sum(Modelname.fieldname) as total_sum'
            )
        )
    );

Perth

Answer 3

Temporarily set the virtualFields prior to doing a find.

$this->MaterialScan->virtualFields = array(
    'total_qty' => 'COUNT(MaterialScan.id)',
    'total_lbs' => 'SUM(MaterialScan.weight)'
);
$materialScans = $this->MaterialScan->find('all',array(
    'conditions' => array(
        'MaterialScan.id' => $scans
    ),
    'group' => array('MaterialScan.part_number')
));

This avoids having the [0] elements in the returned array.

Decollate

Answer 4

You can use virtualFields:

var $virtualFields = array(
    'the_sum' => 'SUM(Model.cost * Model.quantity)'
);

Archfiend

Answer 5

CakePHP has a sum() function among other commonly used SQL functions. See further information in the documentation: "Using SQL Functions".

I didn't use a find('all') and made my solution via Salines' answer to "how to get sum of column in datatable using cakephp". This worked for me. I also replaced your use of PHP superglobals with $this->params['named']['po_id']]. FYI, I am using CakePHP3.

$total = $this->RequestedItem->find();
$total->select([
       'RequestedItem.purchase_request_id',
       'ctotal' => $total->func()->sum('cost * quantity')
   ])
   ->where(['RequestedItem.purchase_request_id' => $this->params['named']['po_id']]);

Lovash