Laravel查询如何将状态字段布尔值1/0转换为'passed'/'failed'文本

直接在查询层通过SQL的CASE语句转换字段值即可，修改后的完整代码如下：

public function getQuizInfoByQuizID(Request $request, $id){
    $remarks = DB::table('user_scores')->where('quiz_id',$id)
        ->join('quiz_information','quiz_information.id', '=', 'quiz_id')
        ->rightJoin('users','users.id', '=', 'user_id')
        ->select(
            'quiz_information.quiz_title AS `Quiz Title`',
            'total_points AS Points',
            'number_of_correct_answers',
            'users.name AS Name',
            DB::raw("CASE WHEN remarks = 1 THEN 'passed' ELSE 'failed' END AS STATUS")
        ) 
        ->groupBy('quiz_title','total_points','number_of_correct_answers','name','remarks')
        ->get()
        ->toArray();

    return response([
        'message'=>"Remarks successfuly shown", 
        'error'=>false,
        'error code'=>200,
        'line'=>"line".__LINE__."".basename(__LINE__),
        'quizRemarks'=>$remarks
    ], 200, [], JSON_NUMERIC_CHECK);
}

补充说明 #

• 带空格的字段别名需要用反引号包裹，避免触发SQL语法错误
• 如果remarks字段存在null值场景，可调整CASE语句适配特殊情况：DB::raw("CASE WHEN remarks = 1 THEN 'passed' WHEN remarks = 0 THEN 'failed' ELSE 'uncompleted' END AS STATUS")
• 不想修改查询SQL也可以在获取集合后遍历转换：

$remarks = DB::table('user_scores')
// 原有查询逻辑保持不变，select部分保留原有的remarks as STATUS
->get()
->map(function($item){
    $item->STATUS = $item->STATUS == 1 ? 'passed' : 'failed';
    return $item;
})
->toArray();

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