Query Counter

Question

Query Counter

Navigation

#1 by (0 votes)

0

I'm learning about cakephp and would like some help.

I have to do the sum of a column in my table (Use Postgres).

I'm trying to use the sum () function of the cake itself.

My controller

use Cake\ORM\TableRegistry;

public function index()
{
   $contratacoes = TableRegistry::get('Contratacoes');
   $query = $contratacoes->find();
   $soma_financiamento = $query->select(['sum'   
    =>$query->func()->sum('vlr_financiamento')]);
   $this->set(['soma_financiamento' => $soma_financiamento]);
}

My view, just to test

<?= $soma_financiamento; ?>

Only this is coming out:

SELECT (SUM(vlr_financiamento)) AS "sum" FROM contratacoes Contratacoes

Someone could help me, please.

cakephp cakephp-3

asked by anonymous 02.06.2017 / 01:58

1 answer

You are not setting the value in the query.setParameter when trying to query the database Inserting data with PHP using classes and PDO

score 0 · Accepted Answer

CakePHP's Query Builder works basically in two steps. The first step you mount is query , in the second step you execute the query and it can return an object from your entity or a collection .

In the example you presented, you are only performing the first step, which is to mount the query. When you give echo to your view , it is writing out the toString method of Query Builder , not executing the query it has set up.

To execute the query and return an object with the data from your database, you can use one of the following methods: all() , toArray() , first() , extract() , and methods that make insert , delete or update method execute() . I recommend testing them to see how it works and how it works.

In your case, you could do as follows:

use Cake\ORM\TableRegistry;

public function index()
{
   $contratacoes = TableRegistry::get('Contratacoes');
   $query = $contratacoes->find();
   $soma_financiamento = $query
      ->select(['sum' =>$query->func()->sum('vlr_financiamento')])
      ->first();

   // Descomente a linha abaixo para ver o resultado da query
   // debug($soma_financiamento);

   $this->set(['soma_financiamento' => $soma_financiamento->sum]);
}

There are other ways to do this. The Query Builder internally stores a colletion with the data of your query . It is populated when a colletion method is called with the current state of its Query Builder .

So another possible solution is:

use Cake\ORM\TableRegistry;

public function index()
{
   $contratacoes = TableRegistry::get('Contratacoes');
   $query = $contratacoes->find();

   $this->set(['soma_financiamento' => $query->sumOf('vlr_financiamento')]);
}

References and recommended reading of CakePHP documentation:

link
link