Laravel : Syntax error or access violation: 1055 Error

Question

I want use WhereIn and Groupby in Same Query to fetch Result.

I've tried this:

$loadids=explode("#@*",$reciptdet->loading_id);
$loadingdatas=DB::table('loading')->groupBy('vehicle_no')->whereIn('id',$loadids)->get();

But I got this error message:

SQLSTATE[42000]: Syntax error or access violation: 1055 'sbrtpt.loading.id' isn't in GROUP BY (SQL: select * from loading where id in (14, 15, 16) group by vehicle_no)

Answer 1

Short answer

In config\database.php --> "mysql" array

Set 'strict' => false to disable all.

.... or

You can leave 'strict' => true and add modes to "mysql" option in

'mysql' => [
       ...
       ....
       'strict' => true,
       'modes' => [
            //'ONLY_FULL_GROUP_BY', // Disable this to allow grouping by one column
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_AUTO_CREATE_USER',
            'NO_ENGINE_SUBSTITUTION'
        ],
 ]

---

Detailed answer

You may not need to disable all strict options ... Kindly have a look on this answer about this issue.

Answer 2

This is probably a SQL_MODE problem. In your config/database.php, in the connection, change

strict => false

As in

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', 'localhost'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
],

Answer 3

Without modifiying config\database.php file

Set 'strict' => false in the config\database.php could be a security issue. So, a simple Laravel solution could be first call get() and then groupBy('vehicle_no):

$loadids = explode("#@*", $reciptdet->loading_id);
$loadingdatas = DB::table('loading')->whereIn('id', $loadids)->get();
$grouped = $loadingdatas->groupBy('vehicle_no');

Answer 4

No need to change any where in your System jut use code like in laravel

\DB::statement("SET SQL_MODE=''");//this is the trick use it just before your query

$data=Task::where('user_id', Auth::user()->id)->where('status', 0)->groupBy('task_code')->get(['id','task_code', 'title']);

Answer 5

Whenever using groupBy in eloquent, always include the column name used in the groupBy function in the select() function.

$loadids=explode("#@*",$reciptdet->loading_id);
$loadingdatas=DB::table('loading')->select('vehicle_no')->groupBy('vehicle_no')->whereIn('id',$loadids)->get();//add select('vehicle_no')

Also it is a bad practice to disable strict mode in the config file. Doing so may cause corrupt data to enter the database such as invalid dates without any warnings.Don't do that unless absolutely necessary.

Answer 6

I was having this problem also but after changing 'strict' => true, to 'strict' => false, the error disappeared.

You can find this setting in:

config\database.php

'mysql' => [
    ...
    'strict' => false,
    ...
]

Answer 7

This restriction makes sense as when you use GROUP BY in MySQL, it returns one row for each value in the columns used in GROUP BY. So, the values of other columns in the selected rows do not make sense to use anywhere. So, it's always recommended to use the best practice and I would recommend not to disable MySQL Strict Mode.

Often developers may need rows of a query grouped by the value of a column. Here they don't need only one row per the unique values of the columns. But they need multiple rows grouped by the unique values of a particular column. For some reason, they use groupBy Query Builder method of Laravel which generates a MySQL GROUP BY query and the developers encounter the above error.

The solution to their problem is to use groupBy Collection method instead. For example,

$loadingData = DB::table('loading')
    ->whereIn('id', $loadIds)
    ->get()
    ->groupBy('vehicle_no');

This will give them the desired result.

Answer 8

update config/database.php

set:

'mysql' => [
           'strict' => false,
        ],

instead of:

'mysql' => [
           'strict' => true,
        ],

and don't forget to clear cache:

php artisan config:cache

Answer 9

This is on laravel doc 8.x , it works well

 $users = DB::table('users')
             ->select(DB::raw('count(*) as user_count, status'))
             ->where('status', '<>', 1)
             ->groupBy('status')
             ->get();

Answer 10

You might as well use:

distinct('vehicle_no')

instead of groupBy('vehicle_no') each case scenario is different, but looking at your query, distinct might be the way to go since you're not aggregating data.

Answer 11

You can leave 'strict' => true and add modes to "mysql" option in.

'mysql' => [
   ...
   ....
   'strict' => true,
   'modes' => [
        //'ONLY_FULL_GROUP_BY', // Disable this to allow grouping by one column
        'STRICT_TRANS_TABLES',
        'NO_ZERO_IN_DATE',
        'NO_ZERO_DATE',
        'ERROR_FOR_DIVISION_BY_ZERO',
        // 'NO_AUTO_CREATE_USER', // This has been deprecated and will throw an error in mysql v8

        'NO_ENGINE_SUBSTITUTION'
    ],
 ]

Answer 12

add \Schema::defaultStringLength(191); to boot method

class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }

    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        //
         \Schema::defaultStringLength(191);
    }
}