problem

Recently I am using Laravel 5.4 as a project, and an error will be reported when using group by in Eloquent ORM to group and query data. The error is as follows:

SQLSTATE[42000]: Syntax error or access violation: 1055 ‘field’ isn’t in GROUP BY

It turned out that the developer added a strict mode for the database after version 5.3. One of the developers had an interesting view on adding this mode:

Adam14Four:
the To BE Completely honest, the I do not at The Remember the What exactly were the Details, But some IT WAS the Sort of the Data-Loss problem.
Honestly, I forgot the specific details, the problem may be due to loss of data sorting .

The following problems will also occur in this mode:

fernandobandeira:
1-Add all columns to group by.
Group by requires all columns.
2-Won’t be able to use date’s such as 0000-00-00 00:00:00.
Time cannot use the data of 0000-00-00 00:00:00.
3 -. Fields like boolean will throw fatal if you pass something that is not a boolean value, like 1, before it would convert it to true and save, with strict it fails
If the field is boolean type, but passed a non-boolean Such as “1” will throw a fatal error. In non-strict mode, it will be automatically converted to true and saved.
4-You’ll get an error if you divide a field by 0 (or another field that has 0 as value).
If a field is divided by 0, you will get an error (or another field with 0 value)

Solution

1.「full group by」

As the name implies: the select query field contains all the group by fields

2. Increase the “strict” key to false

Increase in /config/database.php

'connections' => [
    'mysql' => [
            ...
        'strict' => false,//增加此行
    ],
],

3 /config/database.php configure modes

'connections' => [
    'mysql' => [
            ...
            ...
            ...
        'modes' => [
            'ONLY_FULL_GROUP_BY',
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_AUTO_CREATE_USER',
            'NO_ENGINE_SUBSTITUTION',
        ],
    ],
],