如何在Laravel中将原生数据库查询转换为Query Builder并实现分页

转换逻辑说明 #

• 拆分原生SQL的子查询、关联、条件、聚合、分组逻辑，对应Query Builder的同名方法即可，无法直接链式调用的SQL表达式用DB::raw()包裹
• Laravel Query Builder自带分页能力，调用paginate(每页条数)方法可自动完成分页参数解析、总条数计算、结果截取；超大表场景下可改用simplePaginate()跳过总条数统计，大幅提升查询性能

代码修正说明 #

你提供的代码仅joinSub方法使用有误：joinSub第二个参数为子查询的别名，关联条件需要放到on方法中，修正后完整可运行代码如下：

$cpCounsel = DB::table('cp_counsel as A')
    ->select([
        'A.enrolment_number as id',
        DB::raw('MIN(A.counsel) as counsel'),
    ])
    ->groupBy('enrolment_number');

$counsels = DB::table('cp_cases_counsel as T')
    ->joinSub($cpCounsel, 'A', function ($join) {
        $join->on('A.id', '=', 'T.counsel_id');
    })
    ->where('A.counsel', 'like', "%{$request->search_term}%")
    ->select([
        'T.counsel_id',
        'A.counsel',
        DB::raw('COUNT(T.counsel_id) as total'),
        DB::raw('SUM(if(T.court_id = 2, 1, 0)) as supreme_court_cases'),
        DB::raw('SUM(if(T.court_id = 2, 1, 0) AND if(T.counsel_role = 1, 1, 0)) as supreme_court_cases_as_lead'),
        DB::raw('SUM(if(T.court_id = 2, 1, 0) AND if(T.counsel_role = 2, 1, 0)) as supreme_court_cases_as_supporting'),
        DB::raw('SUM(if(T.court_id = 1, 1, 0)) as appeal_court_cases'),
        DB::raw('SUM(if(T.court_id = 1, 1, 0) AND if(T.counsel_role = 1, 1, 0)) as appeal_court_cases_as_lead'),
        DB::raw('SUM(if(T.court_id = 1, 1, 0) AND if(T.counsel_role = 2, 1, 0)) as appeal_court_cases_as_supporting'),
    ])
    ->groupBy('T.counsel_id', 'A.counsel')
    ->paginate(15);

性能优化建议 #

• 给cp_counsel.enrolment_number、cp_cases_counsel.counsel_id、court_id、counsel_role字段添加联合索引，加快分组和聚合查询速度
• 模糊查询如果前缀是通配符%无法走索引，数据量极大时可考虑用全文检索方案优化检索性能

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