Laravel 8 Query Builder结合DB Raw实现MS SQL的CASE字段拼接查询

解决方案 #

你原有代码未实现原生SQL中提取picture字段路径末尾文件名的核心逻辑，且需注意MSSQL语法与PHP字符串引用的兼容问题，正确代码如下：

基础可用版本 #

use Illuminate\Support\Facades\DB;

// 若实际表名为Staff，请将tblFaculty替换为Staff
$faculties = DB::table('tblFaculty')
    ->select(
        'fname',
        'lname',
        'title',
        DB::raw("CASE 
            WHEN picture IS NULL THEN 'https://www.aaa.com/images/interior/bench2.jpg'
            ELSE 'https://www.aaa.com/images/staff/' + REVERSE(LEFT(REVERSE(picture), CHARINDEX('/', REVERSE(picture)) - 1)) 
        END AS picture")
    )
    ->get();
dd($faculties);

边界兼容版本（推荐） #

如果picture字段可能存在无/的纯文件名场景，可新增判断避免CHARINDEX返回0时触发长度计算错误：

use Illuminate\Support\Facades\DB;

$faculties = DB::table('tblFaculty')
    ->select(
        'fname',
        'lname',
        'title',
        DB::raw("CASE 
            WHEN picture IS NULL THEN 'https://www.aaa.com/images/interior/bench2.jpg'
            WHEN CHARINDEX('/', REVERSE(picture)) = 0 THEN 'https://www.aaa.com/images/staff/' + picture
            ELSE 'https://www.aaa.com/images/staff/' + REVERSE(LEFT(REVERSE(picture), CHARINDEX('/', REVERSE(picture)) - 1)) 
        END AS picture")
    )
    ->get();
dd($faculties);

注意事项 #

• SQL语句内的字符串常量统一使用单引号，外层用双引号包裹DB::raw的内容，避免PHP语法解析冲突
• 请根据实际业务的资源域名、路径前缀调整对应字符串内容

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