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

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