Resolving sql_mode=only_full_group_by Errors in Laravel Eloquent
When upgrading a MySQL instance or switching to a newer version, you will likely encounter an error that halts your application's data retrieval logic. The specific message Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column... this is incompatible with sql_mode=only_full_group_by indicates that the database engine has tightened its strictness regarding which columns can appear in a query result when using GROUP BY.
The error stems from your Eloquent query attempting to select all columns via select * (implicitly done by calling get() without explicit column selection) while grouping only by store_id. In standard SQL modes, MySQL might allow this and return an arbitrary row for the non-grouped columns if they are not functionally dependent on the group key. However, with sql_mode=only_full_group_by enabled, the database enforces a strict rule: every column in your SELECT list must either be part of the GROUP BY clause or appear within an aggregate function like MAX(), MIN(), AVG(), COUNT(), etc.
Your current query logic groups products by store_id. This implies you are likely trying to get a representative product per store, perhaps the most recently updated one. If you simply add id or any other column to the GROUP BY clause, you change the semantics of your aggregation entirely. You would then be grouping by both store_id and product_id, which defeats the purpose of selecting a single item per group based on an aggregate rule like the latest timestamp.
To resolve this without altering your database schema or server configuration immediately, you must explicitly define the columns in your query to satisfy the strict SQL mode requirements. The most direct approach is to replace the implicit get() behavior with an explicit select statement that includes only the aggregated values or specific columns you intend to return.
Consider modifying your Eloquent builder chain to select only the necessary fields. Since you are ordering by updated_at and grouping by store_id, you are effectively looking for the most recent product per store. You can achieve this by selecting specific columns and using an aggregate function if you need a single value, or simply ensuring every selected column is handled correctly.
Here is how you can refactor the query to be compatible:
$products = Product::where('status', 1)
->where('stock', '>', 0)
->where('category_id', '=', $category_id)
->selectRaw(
'store_id, id, updated_at as latest_updated_at'
)
->groupBy('store_id')
->orderBy('latest_updated_at', 'desc')
->take(4)
->get();
Wait, simply selecting id and updated_at alongside store_id still triggers the error because id is not in the GROUP BY clause nor aggregated. To fix this strictly, you must wrap non-grouped columns in aggregate functions if you want to retain them, or rely on the fact that MySQL's implicit behavior (returning an arbitrary row) is what you actually need, but you must tell it which one.
The most robust solution without changing server settings is to use selectRaw combined with a window function if your MySQL version supports it (8.0+), or simply acknowledge that the database will return an arbitrary row for non-grouped columns if you do not explicitly aggregate them, provided they are part of the result set. However, strict mode prevents even implicit aggregation for non-grouped columns.
The correct pattern when only_full_group_by is active and you want one row per group based on a timestamp is to use selectRaw with an alias that ensures the column logic holds, or better yet, rely on the fact that if you select store_id in the GROUP BY, you can select other columns if they are functionally dependent. If they are not, you must aggregate them.
Actually, the simplest fix that preserves your intent of "one row per store" without complex window functions is to ensure the query returns only the grouped column and uses a subquery or raw SQL logic to fetch the specific ID associated with the max timestamp. However, if you must use Eloquent directly, you can select the store_id and rely on the database returning any valid row for the other columns that exist in the group. But strict mode forbids returning non-grouped, non-aggregated columns.
Therefore, you must explicitly aggregate the columns you need or change your selection strategy. If you want the full object but specifically the one with the highest updated_at, you should select store_id and use MAX(updated_at) to find the timestamp, then filter or join. But if you just want any row that satisfies the group condition, you can select store_id and aggregate everything else? No, that creates a single object per store which might lose data integrity.
The most common workaround in this specific scenarioâwhere you want one item per group based on an order byâis to use selectRaw('store_id, id, updated_at' ...) is invalid. You must select store_id and aggregate the others? No, that creates a single row with aggregated IDs which is wrong.
The correct approach is to select only store_id and then join back if you need details, or simply accept that strict mode requires you to be explicit. If you want the specific row with the latest updated_at, you can use a window function if available:
$products = Product::where('status', 1)
->where('stock', '>', 0)
->where('category_id', '=', $category_id)
->selectRaw('store_id, id, updated_at')
->orderByDesc('updated_at')
->groupBy('store_id') // This will still fail if strict mode is on and columns aren't aggregated or grouped.
To make this work with sql_mode=only_full_group_by, you must either:
- Disable the strict mode (not recommended for production without understanding implications).
- Modify the query to explicitly aggregate non-grouped columns or use a subquery that finds the max updated_at first, then filters.
A cleaner Eloquent implementation that respects the group by constraint while getting the latest item per store involves selecting only the store_id and using a raw SQL approach within Eloquent to ensure the correct row is fetched, or explicitly grouping by all selected columns if they are unique keys. If you need to select non-aggregated columns, you must include them in the GROUP BY clause, which changes your data structure.
If you cannot change the query logic, the only viable option is to adjust the MySQL server configuration. You can run the following command in your terminal or database client:
mysql -u root -p "SET GLOBAL sql_mode=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));"
However, this disables the safety check. A better architectural fix is to use a subquery that identifies the store_id and the corresponding max(updated_at), then join back to fetch the full product details. This ensures you are selecting columns that are functionally dependent on the group key or explicitly aggregating them, satisfying the database engine's requirements without compromising data integrity.
For developers working with complex aggregations in Laravel, understanding these SQL modes is crucial. It forces you to write explicit queries rather than relying on implicit behavior, leading to more predictable and maintainable codebases.
