Laravel : Syntax error or access violation: 1055 Error

PhpLaravelLaravel 5Qsqlquery

Php Problem Overview

I want to use WhereIn and Groupby in the 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)

Php Solutions

Solution 1 - Php

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.

Solution 2 - Php

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

Solution 3 - Php

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']);

Solution 4 - Php

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');

Solution 5 - Php

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

Solution 6 - Php

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.

Solution 7 - Php

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

Solution 8 - Php

update config/database.php

set:

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

instead of:

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

and don't forget to clear cache:

php artisan config:cache

Solution 9 - Php

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.

Solution 10 - Php

SQLSTATE[42000]: Syntax error or access violation: 1055  in GROUP BY

If you get the above error, add the following into your database.php file in the config folder:

'mysql' => [
    'strict' => true,
    'modes' => [
        'STRICT_TRANS_TABLES',
        'NO_ZERO_IN_DATE',
        'NO_ZERO_DATE',
        'ERROR_FOR_DIVISION_BY_ZERO',
        'NO_AUTO_CREATE_USER',
        'NO_ENGINE_SUBSTITUTION'
    ],
]

Solution 11 - Php

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.

Solution 12 - Php

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();

Solution 13 - Php

Later, after googling for a while, I learnt that the problem was caused by the sql_mode setting, and modified the configuration of config/database.php as strict => false.

link: https://stdworkflow.com/147/laravel-syntax-error-or-access-violation-1055-error

Solution 14 - Php

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);
    }
}

Solution 15 - Php

I solve a similar issue with using group by by first getting the columns i needed to sort then use the group by.

$SampleData= DB::table('tableExampleName')->get(['col1','col2'])->groupBy('col3');

then you can dd() the values and check if they are the right grouping. You can all assign it and pass it into your view for further testing.

Note: This is using Laravel 8.

Categories
Recommended Php Solutions
Recommended Laravel Solutions
Recommended Laravel 5 Solutions

Previous Solution:

Android Canvas: drawing too large bitmap

Android

Next Solution:

What are the differences between QEMU and VirtualBox?

Virtual MachineVirtualboxQemuXen

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionKarthikvijayaveniView Question on Stackoverflow
Solution 1 - PhpHusamView Answer on Stackoverflow
Solution 2 - PhpAntonio Carlos RibeiroView Answer on Stackoverflow
Solution 3 - PhpSk BindasView Answer on Stackoverflow
Solution 4 - PhpcesponView Answer on Stackoverflow
Solution 5 - PhpZakheleView Answer on Stackoverflow
Solution 6 - PhpThungdemoView Answer on Stackoverflow
Solution 7 - PhpChristopher C OkonkwoView Answer on Stackoverflow
Solution 8 - PhpGouda ElalfyView Answer on Stackoverflow
Solution 9 - PhpDebiprasadView Answer on Stackoverflow
Solution 10 - PhpFredrick KipropView Answer on Stackoverflow
Solution 11 - Phpben.cView Answer on Stackoverflow
Solution 12 - PhpRachid LoukiliView Answer on Stackoverflow
Solution 13 - PhpdrodriguezView Answer on Stackoverflow
Solution 14 - Phphamed hossaniView Answer on Stackoverflow
Solution 15 - PhpZackAttackView Answer on Stackoverflow