如何在Laravel中将复杂原生SQL查询转换为查询构建器并实现分页

<?php

use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request; // 假设 $request 对象可用

// 假设 $request->search_term 已经定义

/**
 * 步骤1：构建子查询 (cpCounsel)
 * 对应 SQL:
 * SELECT A.enrolment_number AS id, MIN(A.counsel) AS counsel
 * FROM cp_counsel AS A
 * GROUP BY enrolment_number
 */
$cpCounsel = DB::table('cp_counsel as A')
    ->select([
        'A.enrolment_number as id',
        DB::raw('MIN(A.counsel) as counsel'), // 使用 DB::raw() 处理 MIN 函数
    ])
    ->groupBy('enrolment_number');

/**
 * 步骤2：构建主查询 (counsels)
 * 对应 SQL:
 * SELECT T.counsel_id, A.counsel, COUNT(T.counsel_id) AS total,
 *        SUM(IF(T.court_id = 2, 1, 0)) AS supreme_court_cases,
 *        ... (其他条件聚合)
 * FROM cp_cases_counsel AS T
 * JOIN ({子查询}) AS A ON A.id = T.counsel_id
 * WHERE A.counsel LIKE '%search_term%'
 * GROUP BY T.counsel_id, A.counsel
 */
$counsels = DB::table('cp_cases_counsel as T')
    // 使用 joinSub() 将子查询作为虚拟表 A 连接
    ->joinSub($cpCounsel, 'A', function ($join) {
        $join->on('A.id', '=', 'T.counsel_id');
    })
    // 添加 where 条件
    ->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);

// $counsels 现在是一个 Illuminate\Pagination\LengthAwarePaginator 实例
// 你可以直接在视图中使用它来渲染分页链接和数据