CAKEPHP - SUM with Paginate Settings

Question

How can I make this work? Or is this possible? I want to add a row just for the total with pagination. I am new to Developing.. Hope you can help me with this problem.. Thanks..

Here is Paginate Function in Model

public function generateSettingsForPager($transportId, $year){
    $conditions['delete_flag'] = 0;

    if($transportId){
        $conditions['transport_id'] = $transportId;
    }

    if($year){
        $conditions['year'] = $year;
    }

    $settings = array (
        'fields' => 'id, year, transport_id, item, description, currency, budget, exchange_rate, all_in_php',
        'limit' => 30,
        'recursive' => -1,
        'order' => array(
            'id' => 'desc'
        ),
        'conditions' => $conditions
    );

    return $settings;
}

My index function in Controller

public function index(){

$getData = $this->request->query;
$transportId  = isset($getData['transport_id']) ? $getData['transport_id'] : null;
$year = isset($getData['year']) ? $getData['year'] : null;

$this->paginate = $this->UpgradingAndModification->generateSettingsForPager($transportId, $year);
$upgradingAndModifications = $this->paginate('UpgradingAndModification');

$this->set('transportId', $transportId);
$this->set('year', $year);
$this->set('upgradingAndModifications', $upgradingAndModifications);

}

This should be the index view.. See screenshot..

http://awesomescreenshot.com/0b94avre47

What I got so far is same with that screenshot but no Total yet.

If you are trying to sum the values for one page's worth of data, do it in the view. If you need the sum for the entire results of the find, regardless of which page you are on, you will need to run a second query which returns the sum of the fields. — AgRizzo
@AbhijeetKambli, yes i want the sum of one field which is 'All In USD'.. — banban

Abhijeet Kambli Abhijeet Kambli · Accepted Answer · 2015-02-09T11:07:46

Try This:

SUM(col1+col2) as total

In paginate settings

 public function generateSettingsForPager($transportId, $year){
    $conditions['delete_flag'] = 0;

    if($transportId){
        $conditions['transport_id'] = $transportId;
    }

    if($year){
        $conditions['year'] = $year;
    }

    $settings = array (
        'fields' => 'id, year, transport_id, item, description, currency, budget, exchange_rate, SUM(col1+col2) as total',
        'limit' => 30,
        'recursive' => -1,
        'order' => array(
            'id' => 'desc'
        ),
        'conditions' => $conditions
    );

    return $settings;
}

CAKEPHP - SUM with Paginate Settings

1 Answers