Laravel - Group by not working

GROUP BY Query Not Working in Laravel 5.3

Problem Statement

When executing the following query in Laravel 5.3, an SQL error occurs:

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

Error Message

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, running the raw SQL query in phpMyAdmin works fine.

Cause of the Issue

The issue is related to MySQL’s strict mode.

By default, MySQL in strict mode requires that every column in the SELECT statement is either inside an aggregate function (like SUM, COUNT) or explicitly mentioned in the GROUP BY clause.
Laravel's query builder uses SELECT *, which includes columns not part of GROUP BY, causing the error.

Solutions

Solution 1: Disable Strict Mode in Laravel

Modify the database configuration file:

Open config/database.php
Locate the mysql connection settings.
Set strict => false

Updated Code (config/database.php)

return [
   'connections' => [
      'mysql' => [
         // Other settings...
         'strict' => false, // Disable strict mode
      ],
   ],
];

Save the file and clear the config cache:

php artisan config:clear

Try running the query again.

Solution 2: Modify SQL Mode in MySQL Configuration

If modifying Laravel’s config/database.php does not work, you can manually change MySQL’s SQL mode:

For Linux/macOS:

Open MySQL configuration file:
```
sudo nano /etc/mysql/mysql.cnf
```

Add or modify the [mysqld] section:

[mysqld]
sql_mode = NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Save and exit (CTRL + X, then Y, then Enter).
Restart MySQL:
```
sudo systemctl restart mysql
```

Solution 3: Modify Laravel’s MySQL Connector (Advanced Users Only)

If you prefer to override Laravel’s strict mode behavior programmatically, update the MySQL Connector file:

Open Illuminate\Database\Connectors\MySqlConnector.php
Locate the strictMode() function and modify it as follows:

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'";
}

Save the file and restart the Laravel application.

Alternative Approach: Modify Your Query

Instead of disabling strict mode, you can modify the query to specify columns explicitly:

$top_performers = DB::table('pom_votes')
    ->select('performer_id', DB::raw('COUNT(*) as votes')) // Aggregate function
    ->groupBy('performer_id')
    ->get();

This ensures that MySQL knows how to handle non-grouped columns.

Conclusion

Disabling strict mode (strict => false) is the easiest fix.
Updating MySQL settings is another option, but it affects the entire database.
Modifying your query to use COUNT() or similar functions is the best long-term solution.

Souy Soeng