[FIXED] Without changing sql mode=only_full_group_by mode how can I execute group by in cakephp?

Issue

I am trying get month wise sum of amount from transactions table. I have written below cakephp function to get my desire output.

public function getLastSixMOnthsExpenses($since_date, $t_type)
{
           $query = $this->find()->select([
                 'month' => $this->find()->func()->monthname([
                   'created' => 'identifier'
                ]),
               'amount' => $this->find()->func()->sum('Transactions.amount')
            ])
            ->where([
               'Transactions.created >='=>  $since_date->modify('6 months ago')->startOfMonth(),
               'Transactions.created <='=>  $since_date->endOfMonth(),
               'Transactions.transaction_type '=>$t_type
            ])
            ->group(['month'])
            ->order(['Transactions.created'=>'ASC'])
           ;
    
           return $query;
 }

I am getting below error

Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'kjoumaa_kamaljoumaa.Transactions.created' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Without change sql mode , How can I run group by here ?

Solution

Order on an aggregate instead.

Since you group by the month, all created fields in those groups will be of one and the same month, eg all created fields in one group will point to either an earlier or a later date than the fields of another group, so you could simply pick either the min or the max value out of a group:

->orderAsc(function (
    \Cake\Database\Expression\QueryExpression $exp,
    \Cake\ORM\Query $query
) {
    return $query->func()->min(
        $query->identifier('Transactions.created')
    );
})

ORDER BY MIN(Transactions.created) ASC

Also if you would select the month as a number instead of as a name, you could order on that field.

Answered By - ndm