The problem

An error has recently been reported while working on a project using Eloquent ORM group by for grouping data. Error as follows:

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

It turns out that the development team has enabled Strict mode by default in their database configuration since Laravel5.3, and one of the developers had an interesting comment on adding this feature:

Adam14Four:


To be completely honest, I don’t remember exactly what the details were, but it was some sort of data-loss problem.


To be honest, I forgot the exact details, probably because of the data loss sorting problem.

This model also presents the following problems:

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.


The time cannot be in 0000-00-00 00:00:00 format.


3 – Fields like boolean will throw fatal if you pass something that isn’t a boolean value, like 1, before it would convert it to true and save, with strict it fails.


If the field is of Boolean type, passing a non-boolean such as “1” will throw a fatal error. In non-strict mode, this is 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).


Dividing a field by 0 will give you an error (or any other field with a value of 0)

The solution

1. “Full group by”

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

2. Set the Strict key to false

In the/config/database. PHP increase

'connections' = > [' mysql' = > [... 'strict' = > false, increase the trip / /, Laravel5.3 + default to true],].Copy the code

3. Selectively turn on qualified modes

In the/config/database. PHP increase

'connections' => ['mysql' => [... 'modes' => [//'ONLY_FULL_GROUP_BY',// close 'full group by' set 'STRICT_TRANS_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'ERROR_FOR_DIVISION_BY_ZERO', 'NO_AUTO_CREATE_USER', 'NO_ENGINE_SUBSTITUTION', ], ], ].Copy the code

PS: If you do not know the constants of the above modes, please refer to the Chinese and English versions

reference

Github.com/laravel/fra… Stackoverflow.com/questions/2… Xstarcd. Making. IO/wiki/MySQL /…



Creative Commons Attribution-NonCommercial 4.0 International License