Group by not working - Laravel

Question

I'm not able to run this simple query in Laravel 5.3

$top_performers = DB::table('pom_votes')
        ->groupBy('performer_id')
        ->get();

It gives me:

SQLSTATE[42000]: Syntax error or access violation: 1055 'assessment_system.pom_votes.id' isn't in GROUP BY (SQL: select * from `pom_votes` group by `performer_id`)

However if I copy raw query from the error and fire directly in PhpMyAdmin, it works fine.

I have already checked this:

https://laravel.com/docs/5.3/queries#ordering-grouping-limit-and-offset

Any help would be appricaited.

Thanks,

Parth Vora

Answer 1

Edit your applications's database config file config/database.php

In mysql array, set strict => false to disable MySQL's strict mode

Answer 2

Maybe your issue is due to the fact that you are using a MySQL server vith version 5.7.5+. From this version on the way GROUP BY works is changed since they make it behave in order to be SQL99 compliant (where in previous versions it was not).

Try to do a full group by or change the configuration of your MySQL server.

Link to official MySQL doc where full GROUP BY is explanined

Answer 3

More safe method instead of disabling strict ('strict' => false) what you could do is pass an array to the config, enabling only the modes that you want:

// config/database.php
    
    'connections' => [
        //...
        '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',
            ],
        ],
    ],

For anybody who is still getting the same error after changing that setting, try clearing the config cache by running php artisan config:cache

Answer 4

Go to config/database.php

Update strict value false.

return [
   'connections' => [
      'mysql' => [
         'strict' => false
       ]
   ]
]

Answer 5

You can define this line before your query, let's suppose you want to use groupBy so for that instead of changing the config strict to false, simply add this line before where you had used groupBy:

\DB::statement("SET SQL_MODE=''");//this is the trick use it just before your query where you have used group by. Note: make sure your query is correct.

//this is just an example code.

$Rspatients = DB::table('reports')
->select(
    DB::raw("day(created_at) as day"),
    DB::raw("Count(*) as total_patients"))

->orderBy("created_at")
->groupBy(DB::raw("day(created_at)"))
->get();

Answer 6

There are ways to fix this

#1

Get only the columns we are grouping by, in this case category_id.

NOTE: Columns in select must be present in groupBy, and vice versa.

$posts = Post::query()
    ->select('category_id')
    ->groupBy('category_id')
    ->get();

category_id
1
2

#2

But I want all columns!

Okay, so you want to get all columns. Then the trick is to simply not use groupBy() on a database level. Instead, you can use it with the returned collection instead.

$posts = Post::query()
    ->get()
    ->groupBy('category_id');

[
 
'1' => [
    ['id' => 1, 'name' => 'Post 1', 'category_id' => 1, 'author_id' => 4 'visits' => 32],
    ['id' => 2, 'name' => 'Post 2', 'category_id' => 1, 'author_id' => 8 'visits' => 12],
],
'2' => [
    ['id' => 3, 'name' => 'Post 3', 'category_id' => 2, 'author_id' => 12 'visits' => 201],
    ['id' => 4, 'name' => 'Post 4', 'category_id' => 2, 'author_id' => 4 'visits' => 0],
],

]

#3

It is possible to simply disable "strict mode" in Laravel, by setting it to false in the database.php config file. While possible I cannot recommend doing so. It is better to spend the time learning how to write proper SQL queries, as the results given by turning "strict mode" off, can be unpredictable and lead to problems down the road.

Reference

https://sinnbeck.dev/posts/laravel-groupby-error

Answer 7

My company uses raw SQL to run group by without risking changing mysql settings.

here is an working example :

public static function getPositivesDaily($start_date, $end_date, $admin_id)
{
    $positives = DB::select(
        'select COUNT(inspections.id) as total,DATE_FORMAT(inspections.created_at, :format) as date
            from inspections
            where inspections.created_at between :start_date and :end_date
            and inspection_results = 1
            and admin_id = :admin_id
            GROUP BY date',
    ['format'=>'%Y-%m-%d', 'start_date'=>$start_date, 'end_date'=> $end_date, 'admin_id'=>$admin_id]
    );


    return $positives;
}

Ask me anything about this code if you don't understand and I will reply as soon as I can. cheers.

Answer 8

If you false strict mode then you can't use other strict functionality to fix this error Go to the Illuminate\Database\Connectors\MySqlConnector.php and change function like below:

protected function strictMode() {
return "set session
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY
_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'";
}

replace function with this.