sum() function in cakephp query

40,523

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)'
);
Share:
40,523

Related videos on Youtube

Keyur Padalia
Author by

Keyur Padalia

I am Website Developer. And I am Using : CakePHP CSS XHTML MySQL JavaScript Amazon MWS Google API

Updated on July 07, 2020

Comments

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

Related