Laravel 10中DB::raw为SQL函数加反引号致查询报错

问题背景 #

从Laravel 9迁移到Laravel 10时，若为兼容修改whereIn中的DB::raw调用，额外添加->getValue(DB::getQueryGrammar())，会导致concat这类SQL函数表达式被自动加上反引号（如\concat(a_col, b_col)``），数据库会将其识别为列名，抛出错误：

Column not found: 1054 Unknown column 'concat(a_col, b_col)' in 'IN/ALL/ANY subquery'

原Laravel 9中的查询示例：

$query = DB::table('table_a')
->join('table_b', 'table_b.id', '=', 'table_a.col_c')
->select([
    'table_b.c_id',
    DB::raw('COUNT(DISTINCT(col_c)) AS any_count'),
    DB::raw('COUNT(CASE WHEN col_b = 1 THEN 1 END) AS one_count'),
    DB::raw('COUNT(CASE WHEN col_b = 2 THEN 1 END) AS two_count'),
])
->where('col_d', '=', ColD::class)
->where('col_e', '=', true)
->whereIn(DB::raw('concat(col_a, col_b, col_c)'), function ($query) {
    $query->from('table_a as latest_table_a')
          ->selectRaw('concat(max(col_a), col_b, col_c) as max_unique_cols')
          ->whereColumn('col_d', 'table_a.col_d')
          ->whereColumn('col_c', 'table_a.col_c')
          ->groupBy('col_d', 'col_c', 'col_b');
})
->groupBy('table_b.c_id');

解决方案 #

方案1：移除getValue调用，直接传递DB::raw实例 #

Laravel 10的查询构建器仍支持直接将DB::raw（即Illuminate\Database\Query\Expression实例）作为whereIn的第一个参数，无需手动调用getValue转成字符串。修正后的代码如下：

$query = DB::table('table_a')
->join('table_b', 'table_b.id', '=', 'table_a.col_c')
->select([
    'table_b.c_id',
    DB::raw('COUNT(DISTINCT(col_c)) AS any_count'),
    DB::raw('COUNT(CASE WHEN col_b = 1 THEN 1 END) AS one_count'),
    DB::raw('COUNT(CASE WHEN col_b = 2 THEN 1 END) AS two_count'),
])
->where('col_d', '=', ColD::class)
->where('col_e', '=', true)
// 直接传递DB::raw实例，无需调用getValue
->whereIn(DB::raw('concat(col_a, col_b, col_c)'), function ($query) {
    $query->from('table_a as latest_table_a')
          ->selectRaw('concat(max(col_a), col_b, col_c) as max_unique_cols')
          ->whereColumn('col_d', 'table_a.col_d')
          ->whereColumn('col_c', 'table_a.col_c')
          ->groupBy('col_d', 'col_c', 'col_b');
})
->groupBy('table_b.c_id');

此方式下查询构建器会正确识别这是原生SQL表达式，不会添加多余反引号。

方案2：改用whereRaw编写完整条件 #

如果需要更灵活的SQL控制，可直接用whereRaw编写整个IN条件的SQL片段，彻底避免查询构建器的自动转义：

$query = DB::table('table_a')
->join('table_b', 'table_b.id', '=', 'table_a.col_c')
->select([
    'table_b.c_id',
    DB::raw('COUNT(DISTINCT(col_c)) AS any_count'),
    DB::raw('COUNT(CASE WHEN col_b = 1 THEN 1 END) AS one_count'),
    DB::raw('COUNT(CASE WHEN col_b = 2 THEN 1 END) AS two_count'),
])
->where('col_d', '=', ColD::class)
->where('col_e', '=', true)
->whereRaw('concat(col_a, col_b, col_c) IN (
    SELECT concat(max(col_a), col_b, col_c)
    FROM table_a as latest_table_a
    WHERE latest_table_a.col_d = table_a.col_d
      AND latest_table_a.col_c = table_a.col_c
    GROUP BY col_d, col_c, col_b
)')
->groupBy('table_b.c_id');

说明 #

之前添加getValue的操作属于冗余操作，Laravel 10的whereIn方法仍支持接收Expression类型参数，调用getValue会将表达式转为普通字符串，导致查询构建器误判为列名并添加反引号。

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