CODEWITHSUNDEEP
cakephpcakephp-3.x
Satu Sultana
Satu Sultana

Reputation: 537

How to get last month row sum in cakephp 3

I need to get last month transactions amount sum , I wrote query but getting error Error: Call to undefined function App\Controller\YEAR() 

// sum of total received amount 
  $conditions['transaction_type']=1;
  $conditions['AND']['YEAR(Transactions.created) >='] = YEAR('CURRENT_DATE - INTERVAL 1 MONTH');
  $conditions['AND']['MONTH(Transactions.created) <='] = MONTH('CURRENT_DATE - INTERVAL 1 MONTH');
  $query = $this->Transactions->find('all',['conditions'=>$conditions]); 
  $collection = new Collection($query);
  $sumOfReceivedAmount =  $collection->sumOf('amount');
  $this->set('sumOfReceivedAmount',$sumOfReceivedAmount);

I have also tried using query builder 

$query = $this->Transactions->find()
               ->where(function ($exp, $q) {
                     $year = $q->func()->year([
                          'created' => 'identifier'
                     ]);
                     $month = $q->func()->month([
                          'created' => 'identifier'
                     ]);
                     return $exp
                     ->eq($year, $q->func()->year(['CURRENT_DATE - INTERVAL 1 MONTH']))
                     ->eq($month, $q->func()->month(['CURRENT_DATE - INTERVAL 1 MONTH']));
 });

For this code I am getting query like 

SELECT 
  *
FROM 
  transactions Transactions 
WHERE 
  (
    year(created) = (
      year(
        'CURRENT_DATE - INTERVAL 1 MONTH'
      )
    ) 
    AND month(created) = (
      month(
        'CURRENT_DATE - INTERVAL 1 MONTH'
      )
    )
  )

Problem is this single quote 'CURRENT_DATE - INTERVAL 1 MONTH'

How can I remove this single quote ?

Upvotes: 1

Views: 895

Answers (1)

ndm
ndm

Reputation: 60493

Similar to your other year() usage where you decalare the passed value to be an identifier, you'd have to declare your other values as literal values (or pass expression objects) so that they are inserted into the query as is, instead of being bound.

['CURRENT_DATE - INTERVAL 1 MONTH' => 'literal']

CakePHP also ships with methods to express similar functionality, specifically the FunctionsBuilder::dateAdd() method. Also when expressing CURRENT_DATE as a function the query builder will be able to convert the SQL for other DBMS like SqlServer, which has no CURRENT_DATE:

$q->func()->year(
    [$q->func()->dateAdd($q->func()->CURRENT_DATE(), -1, 'MONTH')]
)

NOW() should work too:

$q->func()->dateAdd($q->func()->now(), -1, 'MONTH')

See also

Upvotes: 2

Related Questions