MySQL原生查询转Laravel查询失败，求正确实现方案

问题描述 #

需要将以下MySQL原生查询转换为Laravel查询构造器代码：

select * from `mh_sms_authorchecklist` as `a` 
inner join `mh_sms_selectedrevlist` as `s` on `a`.`orderNumber` = `s`.`orderNumber` 
where `a`.`manuscriptStatus` = '24'
and `a`.`submitStatus` = '1' 
and ( select count( 1 ) 
     FROM mh_sms_selectedrevlist as s
        WHERE s.OrderNumber = a.OrderNumber AND   s.editorType =  'Editor' 
        AND  s.editorID =  '516' AND s.scoreSubmit = '1') = 1;

尝试了以下代码但无法正常运行：

DB::table("mh_sms_authorchecklist as a") 
    ->join('mh_sms_selectedrevlist as s', 'a.orderNumber', '=', 's.orderNumber')
    ->where("a.manuscriptStatus", '24')
    ->where("a.submitStatus",  '1')
    ->where(DB::raw("( select count( 1 ) FROM mh_sms_selectedrevlist as s
        WHERE s.OrderNumber = a.OrderNumber AND   s.editorType =  'Editor' 
        AND  s.editorID =  '516' AND s.scoreSubmit = '1') = 1 "))
    ->get();

问题排查与正确代码 #

原代码的问题在于：将DB::raw作为where方法的单一参数时，Laravel会把整个字符串当作字段名解析，而非直接执行原生条件。以下是几种可行的修正方案：

方案一：使用whereRaw直接执行原生条件 #

这是最贴近你原有写法的修正方式：

DB::table('mh_sms_authorchecklist as a')
    ->join('mh_sms_selectedrevlist as s', 'a.orderNumber', '=', 's.orderNumber')
    ->where('a.manuscriptStatus', '24')
    ->where('a.submitStatus', '1')
    ->whereRaw("(SELECT COUNT(1) FROM mh_sms_selectedrevlist as s 
                 WHERE s.orderNumber = a.orderNumber 
                 AND s.editorType = 'Editor' 
                 AND s.editorID = '516' 
                 AND s.scoreSubmit = '1') = 1")
    ->get();

方案二：使用Laravel子查询构造器（更符合框架风格） #

通过子查询构造器编写，可读性和可维护性更强：

DB::table('mh_sms_authorchecklist as a')
    ->join('mh_sms_selectedrevlist as s', 'a.orderNumber', '=', 's.orderNumber')
    ->where('a.manuscriptStatus', '24')
    ->where('a.submitStatus', '1')
    ->where(function ($query) {
        $subQuery = DB::table('mh_sms_selectedrevlist')
            ->whereColumn('orderNumber', 'a.orderNumber')
            ->where('editorType', 'Editor')
            ->where('editorID', '516')
            ->where('scoreSubmit', '1')
            ->selectRaw('COUNT(1)');
        
        $query->whereRaw('? = 1', [$subQuery]);
    })
    ->get();

方案三：用selectSub+having优化查询逻辑 #

先将子查询结果作为字段返回，再通过having筛选计数等于1的数据：

DB::table('mh_sms_authorchecklist as a')
    ->join('mh_sms_selectedrevlist as s', 'a.orderNumber', '=', 's.orderNumber')
    ->select('a.*', 's.*')
    ->selectSub(function ($sub) {
        $sub->from('mh_sms_selectedrevlist')
            ->whereColumn('orderNumber', 'a.orderNumber')
            ->where('editorType', 'Editor')
            ->where('editorID', '516')
            ->where('scoreSubmit', '1')
            ->selectRaw('COUNT(1)');
    }, 'editor_count')
    ->where('a.manuscriptStatus', '24')
    ->where('a.submitStatus', '1')
    ->having('editor_count', '=', 1)
    ->get();

内容的提问来源于stack exchange，提问作者M. Asif Khan