Laravel Query Builder：复杂SQL查询与分页实践

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class CounselController extends Controller
{
    public function getCounselCases(Request $request)
    {
        // 1. 构建子查询：获取每个律师的最小注册号及对应的律师姓名
        // 对应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. 构建主查询：连接子查询并进行复杂聚合
        // 对应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,
        // SUM(if(T.court_id = 2, 1, 0) AND if(T.counsel_role = 1, 1, 0)) as supreme_court_cases_as_lead,
        // ... (其他 SUM(IF...) 表达式)
        // FROM cp_cases_counsel AS T
        // JOIN (<subquery>) 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() 将 $cpCounsel 子查询作为 'A' 加入
            ->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')
            // 应用分页，每页15条记录
            ->paginate(15);

        // 返回结果，通常会传递给视图
        return view('counsels.index', ['counsels' => $counsels]);
    }
}