CODEWITHSUNDEEP
phpsqlcakephp
LucasM
LucasM

Reputation: 45

SQL Sum on single field with cakePHP don't work with paginate()

I need to get the sum of the field "valor", from the table "orcamentos". I'm using this and it is working, but I know that this is not the right way:

//function index() from OrcamentosController.php
$orcamentoSubprojetosTotal = $this->Orcamento->query(
    "SELECT
        SUM(Orcamento.valor) AS TotalOrcamentoSuprojetos
    FROM
        orcamentos AS Orcamento
    WHERE
        Orcamento.subprojeto_id IS NOT NULL;"
    );
$this->set(compact('orcamentoSubprojetosTotal'));

I have found this question cakephp sum() on single field (and others sum() function in cakephp query, using virtual fields to sum values in cakephp), but in the moment I add this line to my controller:

$this->Orcamento->virtualFields['total'] = 'SUM(Orcamento.valor)';

The paginate() stops working and display only one entry, like so:

Page 1 of 1, showing 1 records out of 2 total, starting on record 1, ending on 2

This is my index() function:

public function index($tipoOrcamento = null) {
$this->Orcamento->recursive = 0;

/*
$orcamentoSubprojetosTotal = $this->Orcamento->query(
    "SELECT
        SUM(Orcamento.valor) AS TotalOrcamentoSuprojetos
    FROM
        orcamentos AS Orcamento
    WHERE
        Orcamento.subprojeto_id IS NOT NULL;"
);
$this->set(compact('orcamentoSubprojetosTotal'));
*/


$this->set(compact('tipoOrcamento'));
if($tipoOrcamento == 'subtitulo'){
    $this->set('orcamentos', $this->Paginator->paginate('Orcamento', array('Orcamento.subtitulo_id IS NOT NULL')));
}elseif($tipoOrcamento == 'subprojeto'){
    $this->set('orcamentos', $this->Paginator->paginate('Orcamento', array('Orcamento.subprojeto_id IS NOT NULL')));
}else{
    $this->set('orcamentos', $this->Paginator->paginate('Orcamento'));
}

}

Can I use the query() or someone can help me with the virtual field?

Thank you.

Upvotes: 1

Views: 976

Answers (2)

AD7six
AD7six

Reputation: 66217

Do not use a virtual field

A virtual field is intended for things like:

public $virtualFields = array(
    'name' => 'CONCAT(User.first_name, " ", User.last_name)'
);

If a virtual field is used from something which does not belong to the/a single row - it will not do what you're expecting as evident by the secondary effects on the find call paginate generates.

Use field

Instead, use the field method and pass in the expression:

$integer = $Model->field(
    'SUM(valor)', 
     array('NOT' => array('subprojeto_id' => null))
);

Which will execute:

SELECT SUM(valor) from x where NOT (subprojecto_id IS NULL);

This will also return a scalar value, whereas calling query as shown in the question will return a nested array.

Upvotes: 1

arilia
arilia

Reputation: 9398

of course when you use SUM you'll get a single record

there are two things you can do:

  1. create the virtualField just before the find() call and unset it just after the query.
  2. using 'fields' => arra(...) in your paginator setting and list just the fields you need to retrieve and not the virtualField when you don't want to SUM

Upvotes: 1

Related Questions