Laravel查询构造器SELECT子句使用子查询作为字段的代码失效问题

直接把原生SQL片段写在select方法的普通字符串参数里时，Laravel查询构造器会自动对字符串做字段转义处理（加反引号包裹），导致子查询语法被破坏，无法正常执行。

Laravel提供了专门的原生SQL支持方法，不会对传入的表达式做自动转义，常见有两种实现方式：

方式1：使用DB::raw()包裹子查询 #

该方法会将传入的字符串作为原生SQL片段保留，支持参数绑定避免SQL注入风险，修正后的代码如下：

$listed_waiting_approach = DB::table('be_medical_insurance_for_foreigners')
    ->join('be_product','be_product.id','=','be_medical_insurance_for_foreigners.product_id')
    ->join('be_status','be_status.id','=','be_medical_insurance_for_foreigners.payment_status_id')
    ->where('be_medical_insurance_for_foreigners.is_approved','=',false)
    ->where('be_medical_insurance_for_foreigners.is_active','=',true)
    ->where('be_medical_insurance_for_foreigners.payment_status_id','=',$payment_status_id1)
    ->where('be_medical_insurance_for_foreigners.order_status_id','=',$order_status_id1)
    ->select(
        'be_medical_insurance_for_foreigners.*',
        'be_product.name as productname',
        'be_medical_insurance_for_foreigners.is_approved as approve',
        'be_status.code as statuscode',
        DB::raw("(SELECT code FROM be_status WHERE id = ?) as paymentname", [$payment_status_id1])
    )
    ->get();

方式2：使用selectSub()方法 #

该方法是Laravel专门为select子查询设计的封装方法，自动处理子查询的语法和参数绑定，安全性和可读性更优：

// 先定义独立的子查询逻辑
$paymentNameSub = DB::table('be_status')
    ->select('code')
    ->where('id', $payment_status_id1);

$listed_waiting_approach = DB::table('be_medical_insurance_for_foreigners')
    ->join('be_product','be_product.id','=','be_medical_insurance_for_foreigners.product_id')
    ->join('be_status','be_status.id','=','be_medical_insurance_for_foreigners.payment_status_id')
    ->where('be_medical_insurance_for_foreigners.is_approved','=',false)
    ->where('be_medical_insurance_for_foreigners.is_active','=',true)
    ->where('be_medical_insurance_for_foreigners.payment_status_id','=',$payment_status_id1)
    ->where('be_medical_insurance_for_foreigners.order_status_id','=',$order_status_id1)
    ->select(
        'be_medical_insurance_for_foreigners.*',
        'be_product.name as productname',
        'be_medical_insurance_for_foreigners.is_approved as approve',
        'be_status.code as statuscode'
    )
    ->selectSub($paymentNameSub, 'paymentname')
    ->get();

当前查询已经通过join关联了be_status表，且where条件已经限定payment_status_id固定为$payment_status_id1，子查询要获取的code值和现有join拿到的be_status.code完全一致，完全可以直接复用该字段作为paymentname的返回值，不需要额外执行子查询，降低数据库查询开销。只需要在select列表中追加一行be_status.code as paymentname即可，返回结果和子查询完全相同。

内容的提问来源于stack exchange，提问作者Cansu Koç