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',
        ],
    ],
],

Similar Posts:

• Laravel Syntax error or access violation: 1055 ‘***’ isn’t in GROUP BY
• [Solved] Laravel Groupby error: laravel which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
• [Solved] MYSQL Command gruop by Error: this is incompatible with sql_mode=only_full_group_by
• SELECT list is not in GROUP BY clause and contains nonaggregated column
• [Solved] SELECT list is not in GROUP BY clause and contains nonaggregated
• SELECT list is not in GROUP BY clause and contains nonaggregated
• [Solved] MySQL Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre
• Mysql Error: 1140 – In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column ‘a.store’; this is incompatible with sql_mode=only_full_group_by
• [Solved] MYSQL8 group by Error: Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY