Syntax error or access violation in Laravel: 1055 Error

Problem

To get Result, I want to utilize WhereIn and Groupby in the same query.

I’ve tried this:

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

However, I received the following error message:

Asked by Karthikvijayaveni

Solution #1

Array “mysql” in configdatabase.php

To disable all, set’strict’ => false.

You can leave’strict’ => true and add modes to the “mysql” option in the “mysql” option in the “mysql” option.

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

It’s possible that you won’t need to disable all of the strict settings… Please take a look at this response to this question.

Answered by Husam

Solution #2

This is most likely a SQL MODE issue. Change the connection in your config/database.php file.

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

Answered by Antonio Carlos Ribeiro

Solution #3

There’s no need to make any changes to your system; simply 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']);

Answered by Sk Bindas

Solution #4

It’s possible that setting’strict’ => false in configdatabase.php is a security risk. As an example, a basic Laravel approach could be to run get() first, then groupBy(‘vehicle no):

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

Answered by cespon

Solution #5

I was having the same issue, but the error went away after changing’strict’ => true to’strict’ => false.

This option can be found in:

config\database.php

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

Answered by Zakhele

Post is based on https://stackoverflow.com/questions/40917189/laravel-syntax-error-or-access-violation-1055-error