Fixing Laravel SQLSTATE[42000] [1055]: Syntax Error (ONLY_FULL_GROUP_BY)
When executing aggregation queries in Laravel, you may encounter an immediate PDO database crash:
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'laravel_app.orders.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
This error is enforced by MySQL 5.7 and MySQL 8.0’s default SQL mode: ONLY_FULL_GROUP_BY. It prevents the database engine from returning arbitrary, non-deterministic values for columns that are neither grouped nor explicitly aggregated.
Quick Diagnostic Reference
| Approach | When to Use | Trade-off |
|---|---|---|
| SQL Refactoring (Recommended) | Production applications handling financial, reporting, or audit data | Fully deterministic, strict SQL standards compliant |
ANY_VALUE() Aggregation |
When you want a representative value without restructuring columns | Supported natively in MySQL 5.7+ |
| Disable Strict Mode in Laravel | Rapid local prototyping or legacy migrations | Can lead to unpredictable column values across rows |
The Broken Query Pattern
Consider an query attempting to fetch the latest order per customer:
// BROKEN: 'created_at' and 'total' are not in GROUP BY or wrapped in aggregate functions
$orders = DB::table('orders')
->select('customer_id', 'total', 'created_at')
->groupBy('customer_id')
->get();
Because multiple orders exist per customer_id, MySQL does not know which order’s total or created_at timestamp should accompany each grouped customer_id. Without ONLY_FULL_GROUP_BY, MySQL picks arbitrary values from the first row found. With ONLY_FULL_GROUP_BY enabled, it aborts the query.
Solution 1: Refactor Using Proper Aggregates (Production Best Practice)
Instead of turning off database safety checks, rewrite the query so every selected column is either in the groupBy clause or enclosed inside an aggregate function (MAX(), MIN(), SUM(), AVG()):
use Illuminate\Support\Facades\DB;
$summary = DB::table('orders')
->select(
'customer_id',
DB::raw('COUNT(id) as total_orders'),
DB::raw('SUM(total) as revenue'),
DB::raw('MAX(created_at) as latest_order_date')
)
->groupBy('customer_id')
->get();
If you need the entire model record representing the latest entry, use a window function (ROW_NUMBER()) or an Eloquent subquery rather than grouping raw models:
$latestOrders = Order::whereIn('id', function ($query) {
$query->select(DB::raw('MAX(id)'))
->from('orders')
->groupBy('customer_id');
})->get();
Solution 2: Use MySQL’s ANY_VALUE() Function
If you have a column where any arbitrary matching value is acceptable, MySQL provides the ANY_VALUE() wrapper specifically to satisfy ONLY_FULL_GROUP_BY:
$orders = DB::table('orders')
->select(
'customer_id',
DB::raw('ANY_VALUE(status) as sample_status'),
DB::raw('MAX(total) as highest_order')
)
->groupBy('customer_id')
->get();
Solution 3: Disable Strict Mode in config/database.php
If you are working with an existing legacy codebase where rewriting queries is not immediately practical, you can instruct Laravel to omit ONLY_FULL_GROUP_BY from its MySQL session modes.
Open config/database.php and inspect your MySQL connection configuration:
Option A: Disable Strict Mode Completely
'mysql' => [
'driver' => 'mysql',
// ...
'strict' => false,
],
Option B: Keep Strict Mode Enabled but Exclude ONLY_FULL_GROUP_BY (Safer)
If you want to maintain strict type checking and zero-date prevention while only suppressing Error 1055, explicitly define the session modes:
'mysql' => [
'driver' => 'mysql',
// ...
'strict' => true,
'modes' => [
'STRICT_TRANS_TABLES',
'NO_ZERO_IN_DATE',
'NO_ZERO_DATE',
'ERROR_FOR_DIVISION_BY_ZERO',
'NO_ENGINE_SUBSTITUTION',
// 'ONLY_FULL_GROUP_BY' is intentionally excluded
],
],
After modifying config/database.php, flush your application cache:
php artisan config:clear
php artisan config:cache
Prevention Checklist
- Always verify if a
groupByquery needs an aggregate (COUNT,SUM,MAX) on non-grouped attributes. - For top-N or latest-by-group queries, prefer correlated subqueries or window functions over naked
groupBycalls. - If adjusting
modesinconfig/database.php, excludeONLY_FULL_GROUP_BYexplicitly rather than disablingstrict => falseacross the entire database.