sum() function in cakephp query
Solution 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
Solution 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'
)
)
);
Solution 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.
Solution 4
You can use virtualFields:
var $virtualFields = array(
'the_sum' => 'SUM(Model.cost * Model.quantity)'
);
Related videos on Youtube
Keyur Padalia
I am Website Developer. And I am Using : CakePHP CSS XHTML MySQL JavaScript Amazon MWS Google API
Updated on July 07, 2020Comments
-
Keyur Padalia over 3 years
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'] ] ] );