Laravel 5.6中LEFT OUTER JOIN正确实现及分组报错解决

Let's break down what's happening here and how to resolve this issue quickly.

Why the Error Pops Up #

The error you're facing is tied to MySQL's only_full_group_by SQL mode (enabled by default in most modern MySQL setups). This mode enforces strict SQL standards: any non-aggregated column in your SELECT clause must either be included in the GROUP BY clause, or be functionally dependent on the columns in GROUP BY.

When you uncomment the stock_alerts.minimum select and LEFT JOIN, MySQL can't confirm that every row in your grouped result will have the same minimum value (even if logically each stock_part only has one alert entry, MySQL doesn't make that assumption automatically).

Your Fix Options #

1. Add minimum to the GROUP BY Clause

If each stock_part has exactly one matching entry in stock_alerts, you can safely add stock_alerts.minimum to your GROUP BY list. This tells MySQL you're grouping by both the stock part ID and its minimum threshold, which complies with the mode:

$q = DB::table('stock_items') 
    ->selectRaw('stock_parts.title') 
    ->selectRaw('COUNT(*) as qtyAvailable') 
    ->selectRaw('SUM(shipments.item_cost) as totalValue') 
    ->selectRaw('stock_alerts.minimum AS minimum') 
    ->join('stock_parts', 'stock_items.stock_part_id', '=', 'stock_parts.id') 
    ->join('shipments', 'shipments.id', '=', 'stock_items.shipment_id') 
    ->leftJoin('stock_alerts', 'stock_alerts.stock_part_id', '=', 'stock_items.stock_part_id') 
    ->whereNull('stock_items.status') 
    ->where('stock_items.current_stock_id', '=', $stockId) 
    ->groupBy('stock_parts.id', 'stock_alerts.minimum') // Include minimum here
    ->get();

2. Wrap minimum in an Aggregate Function

If there's a chance a stock_part could have multiple stock_alerts entries (or you prefer not to modify the GROUP BY clause), use an aggregate function like MIN() to get a single valid value per grouped stock part. Aggregate results don't need to be included in GROUP BY:

$q = DB::table('stock_items') 
    ->selectRaw('stock_parts.title') 
    ->selectRaw('COUNT(*) as qtyAvailable') 
    ->selectRaw('SUM(shipments.item_cost) as totalValue') 
    ->selectRaw('MIN(stock_alerts.minimum) AS minimum') // Aggregate with MIN()
    ->join('stock_parts', 'stock_items.stock_part_id', '=', 'stock_parts.id') 
    ->join('shipments', 'shipments.id', '=', 'stock_items.shipment_id') 
    ->leftJoin('stock_alerts', 'stock_alerts.stock_part_id', '=', 'stock_items.stock_part_id') 
    ->whereNull('stock_items.status') 
    ->where('stock_items.current_stock_id', '=', $stockId) 
    ->groupBy('stock_parts.id') 
    ->get();

3. Disable only_full_group_by (Not Recommended)

You could tweak your MySQL config to remove only_full_group_by from the sql_mode, but this is a bad practice. It bypasses SQL standards and can lead to inconsistent, unexpected query results as your dataset grows.

Why Your Raw SQL Might Have Worked #

If your raw SQL didn't throw this error, it's likely that the sql_mode for that specific MySQL session didn't include only_full_group_by. Laravel uses your MySQL server's default sql_mode, which probably has this strict mode enabled.

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