How to Fix \'Expression #1 of SELECT list is not in GROUP BY clause\' Error in Laravel Eloquent Due to MySQL Strict Mode?

Incompatibility with sql_mode=only_full_group_by in Laravel Eloquent

Encountering the error "Expression #1 of SELECT list is not in GROUP BY clause..." when executing an Eloquent query with grouping suggests an incompatibility with MySQL's sql_mode=only_full_group_by. To resolve this:

Disable MySQL Strict Mode

One solution is to disable the MySQL strict mode setting in your database connection configuration. In Laravel's .env file, add the following line:

DB_STRICT_MODE=false

Alternatively, you can configure the strict mode in your config/database.php file:

<code class="php">'mysql' => [
    // Disable strict mode
    'strict' => false,
],</code>

Explanation

In MySQL 5.7 and later, the sql_mode=only_full_group_by mode requires that all columns in the SELECT list be either included in the GROUP BY clause or be aggregated functions. In the provided query:

<code class="php">$products = Product::where('status', 1)
            ->where('stock', '>', 0)
            ->where('category_id', '=', $category_id)
            ->groupBy('store_id')
            ->orderBy('updated_at', 'desc')
            ->take(4)
            ->get();</code>

The column id (primary key) appears in the SELECT list but is not included in the GROUP BY clause. By disabling strict mode, MySQL will allow non-aggregated columns in the SELECT list that are not functionally dependent on the GROUP BY columns.

The above is the detailed content of How to Fix 'Expression #1 of SELECT list is not in GROUP BY clause' Error in Laravel Eloquent Due to MySQL Strict Mode?. For more information, please follow other related articles on the PHP Chinese website!