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)

Solution 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.

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

Solution 3

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

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

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 6

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

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

Solution 9

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 10

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 11

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

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 13

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 14

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

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.