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