Laravel按owner_name去重查询全字段报错问题求助

Hey there! Let's break down why this error is popping up and how to fix it properly.

Why the Error Happens #

Your raw SQL works because your MySQL server probably doesn't have the ONLY_FULL_GROUP_BY sql_mode enabled (or you're on an older MySQL version that lets non-standard GROUP BY queries slide). But Laravel turns on MySQL's strict mode by default (check config/database.php under the mysql config's strict setting), which enforces the SQL standard: any non-aggregated column in your SELECT clause has to be included in the GROUP BY clause.

When you run Product::select('*')->groupBy('owner_name'), you're pulling all columns (like id, which isn't in GROUP BY), so MySQL throws the 1055 error to enforce this rule.

Solutions to Get Unique Records per owner_name #

1. Use a Subquery for Valid, Standard Compliant Results (Recommended)

The right way to grab one unique record per owner_name (while following SQL rules) is to first identify a unique id for each owner, then fetch the full record. For example, if you want the earliest record (smallest id) per owner:

$uniqueProducts = Product::select('*')
    ->whereIn('id', function ($query) {
        $query->selectRaw('MIN(id)')
              ->from('product')
              ->groupBy('owner_name');
    })
    ->orderBy('id', 'asc')
    ->get();

This query first gets the minimum id for each owner_name, then pulls the full product records matching those IDs. Swap MIN(id) with MAX(id) if you want the latest record instead.

2. Temporarily Disable Strict Mode (Not Recommended)

If you absolutely need to stick to your original query structure (though this breaks SQL standards and can lead to unpredictable results), you can turn off Laravel's strict mode:

• Open config/database.php
• Find the mysql config array, set 'strict' => false
• Clear your config cache with php artisan config:clear

⚠️ Heads up: This is a quick band-aid, not a long-term fix. Disabling strict mode can hide other SQL issues and make your code less portable across different database setups.

Why Your Raw SQL Worked #

Your raw query runs because your MySQL server's sql_mode doesn't include ONLY_FULL_GROUP_BY. Laravel's strict mode automatically enables this (plus other strict settings) when generating queries via the ORM, which is why the Laravel code throws an error even though the raw SQL doesn't.

内容的提问来源于stack exchange，提问作者Mac Taylor