Laravel Query Builder GroupBy Syntax error or access violation

Hello,

Recently I forked a Github project and was updating it. I faced strange issue in using GroupBy in query builder. I was trying to query model as follow.

$projects = ProjectReviews::groupBy('project_id')->orderBy('created_at','DESC')->get();

Basically I wanted to get recently reviewed projects and show dates. But in above query I was getting error Syntax error or access violation project_reviews.id isn't in group by.

That was really strange issue as that's the primary key of table and it should not be part of group by. If you run that query directly in PHP MyAdmin it was working fine. So I was not sure about this. Finally after spending couple of hours I was able to find out the problem.

It's because of strict config settings of database. If you look into config/database.php file there is strict config there.

        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

You have to set to false to make GroupBy working.

Following is mentioned in MySql Documentation for this config.

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.

So basically it secures your database operations. However in Laravel it's not working properly. So I was not sure if it's laravel bug. But I had to disable it make this query working.

Disabling it wouldn't make your web app unsecured if you handle all validations in your controllers and follow best practices like Laravel already does.

However I still recommend to be careful, while using this.