Search code examples
cakephp

sum() function in cakephp query

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']
         ]
     ]
);
Solution

  • 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