不修改sql_mode=only_full_group_by时如何在CakePHP中正常执行group by查询

错误原因 #

开启sql_mode=only_full_group_by后，MySQL要求ORDER BY子句中的字段要么属于GROUP BY的字段列表，要么是经过聚合函数计算的结果。你当前代码GROUP BY的是月份名称month，但ORDER BY直接使用了未聚合的原始字段Transactions.created，不符合该规则，所以触发报错。
另外你的原有代码还存在潜在逻辑问题：如果统计周期跨年，monthname会把不同年份的相同月份数据合并，导致统计结果错误。

修复方案 #

推荐使用更严谨的写法，新增年、月数值字段用于分组和排序，同时解决报错和潜在的跨年统计错误：

public function getLastSixMOnthsExpenses($since_date, $t_type)
{
    $query = $this->find()->select([
            'year' => $this->find()->func()->year([
                'created' => 'identifier'
            ]),
            'month_num' => $this->find()->func()->month([
                'created' => 'identifier'
            ]),
            '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(['year', 'month_num', 'month'])
        // 用分组内的年、月字段排序，符合only_full_group_by规则
        ->order(['year' => 'ASC', 'month_num' => 'ASC'])
    ;

    return $query;
}

如果不需要保留年字段，也可以用简化方案，直接对created用聚合函数包裹后排序：

// 仅修改order子句即可
->order(['MIN(Transactions.created)' => 'ASC'])

两种方案都不需要修改sql_mode配置即可正常运行。

内容的提问来源于stack exchange，提问作者Niloy Rony