SQL sum在具有cakePHP的单个字段上不能与paginate（） [英] SQL Sum on single field with cakePHP don't work with paginate()

问题描述

我需要获得字段valor的总和，从表orcamentos。
我使用这个，它是工作，但我知道这不是正确的方法：

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

我发现这个问题 cakephp sum（）on single field （and others sum（）函数在cakephp查询，使用虚拟字段对cakephp中的值进行求和），但是在这一刻，我将此行添加到我的控制器：

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

paginate（）停止工作，只显示一个条目，例如：

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

第1页，共1页，显示1条记录，从记录1开始，结束于2

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

这是我的index（）函数：

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

}

推荐答案

不要使用虚拟字段

A 虚拟字段用于以下内容：

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

如果使用的虚拟字段不属于/ a单行，没有做你期望的，通过对 paginate 生成的辅助效果显而易见。

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.

请改用字段方法并传递表达式：

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

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

这将执行：

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.

这篇关于SQL sum在具有cakePHP的单个字段上不能与paginate（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！