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