如何使用Laravel Eloquent编写含COALESCE、IF及复杂ON子句的SQL查询

首先要告诉你：你当前的Eloquent写法大体是正确的，完全匹配原SQL的逻辑，可以正常运行。不过有几个可以优化的点，让代码更规范、更安全，也更符合Laravel的最佳实践。

一、现有写法的正确性确认 #

你的代码完美还原了原SQL的核心逻辑：

• 三个LEFT JOIN的关联关系和条件完全对应，尤其是prices表的多条件ON子句，用闭包的方式实现是正确的；
• WHERE子句的筛选条件和原SQL一致；
• SELECT里的finalPrice计算逻辑和原SQL的COALESCE(IF(...))完全相同。

二、优化建议与更规范的实现 #

1. 避免直接拼接变量，防止SQL注入

你当前在DB::raw里直接拼接$customer_group_id，虽然在某些场景下没问题，但存在SQL注入风险。Laravel提供了selectRaw方法支持参数绑定，更安全：

ProductCategory::leftjoin('products', 'product_categories.product_id', '=', 'products.id')
    ->leftJoin('product_translations', 'product_translations.product_id', '=', 'products.id')
    ->leftJoin('prices', function($join) use($customer_group_id) {
        // 这里可以用where替代第二个on，效果完全一致，写法更直观
        $join->on('products.id', '=', 'prices.product_id')
             ->where('prices.customer_group_id', '=', $customer_group_id);
    })
    // 用selectRaw绑定参数，避免SQL注入
    ->selectRaw(
        'coalesce(if(prices.customer_group_id = ?, prices.price, NULL), products.price) AS finalPrice',
        [$customer_group_id]
    )
    ->select('prices.customer_group_id', 'products.*', 'product_translations.*')
    ->where('product_translations.locale', '=', $locale)
    ->where('products.online', true)
    ->where('products.sellable', true)
    ->where('category_id', '=', $this->id);

2. 利用模型关联简化代码（可选）

如果你的模型已经定义了关联关系（比如ProductCategory关联Product，Product关联ProductTranslation和Price），可以用Eloquent的关联查询写法，让代码更简洁：

// 假设模型关联已定义：
// ProductCategory hasMany Product
// Product hasMany ProductTranslation
// Product hasMany Price

ProductCategory::where('category_id', $this->id)
    ->with(['products' => function($productQuery) use($customer_group_id, $locale) {
        $productQuery->where('online', true)
                     ->where('sellable', true)
                     ->with(['productTranslations' => function($transQuery) use($locale) {
                         $transQuery->where('locale', $locale);
                     }])
                     ->leftJoin('prices', function($join) use($customer_group_id) {
                         $join->on('products.id', '=', 'prices.product_id')
                              ->where('prices.customer_group_id', $customer_group_id);
                     })
                     ->selectRaw(
                         'products.*, coalesce(if(prices.customer_group_id = ?, prices.price, NULL), products.price) AS finalPrice, prices.customer_group_id',
                         [$customer_group_id]
                     );
    }])
    ->get();

注意：这种写法返回的是嵌套结构的集合（每个分类下包含对应的产品），和原SQL的扁平结果集不同，适合需要关联模型结构的场景；如果需要和原SQL一致的扁平结果，还是用第一种join写法更合适。

3. 避免使用*选择所有字段

products.*和product_translations.*可能会导致字段冲突（比如两张表都有name字段时，后面的字段会覆盖前面的），建议明确指定需要的字段，比如：

->select('products.id', 'products.name', 'products.price', 'product_translations.name as locale_name', ...)

总结 #

你的初始写法是正确的，优化后的版本在安全性和可读性上更优，你可以根据自己的场景选择合适的实现方式。

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