Laravel 10中转换MAX+CASE WHEN查询报错，如何正确实现？

场景说明 #

需要将worksteps表中subprocess_id为8010的步骤名称按step_no转置为多列，原生SQL可正常运行，但Laravel查询构造器写法报错。

正常运行的原生SQL #

SELECT
  subprocess_id,
  MAX(CASE WHEN step_no = '1' THEN s_name END) AS first_step,
  MAX(CASE WHEN step_no = '2' THEN s_name END) AS second_step,
  MAX(CASE WHEN step_no = '3' THEN s_name END) AS thrid_step,
  MAX(CASE WHEN step_no = '4' THEN s_name END) AS fourth_step,
  MAX(CASE WHEN step_no = '5' THEN s_name END) AS fifth_step,
  MAX(CASE WHEN step_no = '6' THEN s_name END) AS sixth_step
FROM
  worksteps
WHERE subprocess_id=8010
GROUP BY
  subprocess_id;

错误的Laravel写法及报错 #

错误代码：

DB::table("worksteps")
->select("subprocess_id", "max (case when step_no = '1' then s_name end) as `first_step`", "max (case when step_no = '2' then s_name end) as `second_step`", "max (case when step_no = '3' then s_name end) as `thrid_step`", "max (case when step_no = '4' then s_name end) as `fourth_step`", "max (case when step_no = '5' then s_name end) as `fifth_step`", "max (case when step_no = '6' then s_name end) as `sixth_step`")
->where("subprocess_id", "=", 8010)
->groupBy("subprocess_id")
->get();

报错信息：

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'max...

错误原因 #

Laravel的select方法会把普通字符串参数解析为列名，而非SQL表达式。直接传入max (case ...)这类带函数的字符串，框架会误认为这是一个列名，导致找不到对应列的错误。

正确写法 #

方法一：使用DB::raw()包裹聚合表达式

在控制器的show方法中，用DB::raw()标记原生SQL片段，避免被框架解析为列名：

public function show()
{
    $result = DB::table('worksteps')
        ->select(
            'subprocess_id',
            DB::raw("MAX(CASE WHEN step_no = '1' THEN s_name END) AS first_step"),
            DB::raw("MAX(CASE WHEN step_no = '2' THEN s_name END) AS second_step"),
            DB::raw("MAX(CASE WHEN step_no = '3' THEN s_name END) AS thrid_step"),
            DB::raw("MAX(CASE WHEN step_no = '4' THEN s_name END) AS fourth_step"),
            DB::raw("MAX(CASE WHEN step_no = '5' THEN s_name END) AS fifth_step"),
            DB::raw("MAX(CASE WHEN step_no = '6' THEN s_name END) AS sixth_step")
        )
        ->where('subprocess_id', 8010)
        ->groupBy('subprocess_id')
        ->get();

    return $result;
}

方法二：使用selectRaw()简化写法

直接通过selectRaw()传入完整的原生SELECT语句，写法更简洁：

public function show()
{
    $result = DB::table('worksteps')
        ->selectRaw("
            subprocess_id,
            MAX(CASE WHEN step_no = '1' THEN s_name END) AS first_step,
            MAX(CASE WHEN step_no = '2' THEN s_name END) AS second_step,
            MAX(CASE WHEN step_no = '3' THEN s_name END) AS thrid_step,
            MAX(CASE WHEN step_no = '4' THEN s_name END) AS fourth_step,
            MAX(CASE WHEN step_no = '5' THEN s_name END) AS fifth_step,
            MAX(CASE WHEN step_no = '6' THEN s_name END) AS sixth_step
        ")
        ->where('subprocess_id', 8010)
        ->groupBy('subprocess_id')
        ->get();

    return $result;
}

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