Laravel查询构建器：复杂SQL与分页优化技巧

在Laravel开发中，尽管直接编写原始SQL语句能够实现任何数据库操作，但它常常伴随着可读性差、难以维护、易受SQL注入攻击以及难以实现诸如分页等高级功能的缺点。Laravel的查询构建器（Query Builder）提供了一种更具表现力、更安全且更易于管理数据库查询的方式。对于那些包含子查询、复杂聚合和条件逻辑的原始SQL，将其转换为Query Builder表达式是提升代码质量的关键一步。

核心转换策略

将复杂的原始SQL转换为Laravel Query Builder表达式，主要涉及以下几个关键策略：

1. 子查询的优雅处理：joinSub()

当你的查询中包含子查询（Subquery）时，Laravel的joinSub()方法是实现这一目标的理想选择。它允许你将一个完整的Query Builder实例作为子查询，并将其作为一张表与主查询进行连接。

示例分析： 在原始问题中，cpCounsel 部分就是一个子查询，它首先对 cp_counsel 表进行分组并选出最小的 counsel 值。

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

这个子查询随后被主查询用作一张虚拟表进行连接。joinSub() 方法的第一个参数是子查询的Query Builder实例，第二个参数是子查询在主查询中的别名，随后的参数定义了连接条件。

// ... 主查询中调用 joinSub
->joinSub($cpCounsel, 'A', function ($join) {
    $join->on('A.id', '=', 'T.counsel_id');
})
// 注意：在Laravel 8.x+版本中，joinSub的第二个参数直接是子查询的别名，
// 且连接条件通常直接作为后续参数传递，如 'A.id', '=', 'T.counsel_id'。
// 这里的 'A' 是子查询的别名，而不是原始表别名。
// 如果子查询本身有别名，如 'A'，那么joinSub的第二个参数就用这个别名。
// 在本例中，原始SQL的joinSub是 `joinSub($cpCounsel, 'A.id', '=', 'T.counsel_id')`
// 这意味着它将 $cpCounsel 视为别名为 'A' 的表，并使用 A.id 进行连接。
// 因此，正确的写法是：
->joinSub($cpCounsel, 'A', 'A.id', '=', 'T.counsel_id')

这里的 A 是 cpCounsel 这个子查询的别名，它使得我们可以在主查询中引用 cpCounsel 结果集中的字段，例如 A.counsel。

2. 复杂聚合与条件逻辑：DB::raw() 的应用

Query Builder 提供了 count(), sum(), min(), max(), avg() 等基本聚合函数。然而，当需要执行更复杂的聚合逻辑，例如条件求和（SUM(IF(condition, 1, 0))）或使用数据库特有的函数时，DB::raw() 方法就变得不可或缺。它允许你直接插入原始的SQL表达式到查询中。

示例分析： 在提供的查询中，有多个复杂的聚合字段：

• 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

这些表达式都不能直接通过Query Builder的简单方法实现，因此需要使用 DB::raw() 进行包裹：

->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() 表达式
])

DB::raw() 内部的字符串会直接被插入到SQL查询中，因此你需要确保其SQL语法是正确的。

3. 高效数据分页：paginate() 方法

将原始SQL转换为Query Builder最直接的优势之一就是能够方便地使用 paginate() 方法。这个方法会自动处理SQL的 LIMIT 和 OFFSET 子句，并生成用于前端分页链接所需的所有信息。

在Query Builder的链式调用末尾直接调用 paginate(perPage) 即可：

// ... 其他查询条件和聚合
->groupBy('T.counsel_id', 'A.counsel')
->paginate(15); // 每页显示15条记录

paginate() 方法返回一个 LengthAwarePaginator 实例，其中包含了当前页的数据、总记录数、每页数量、当前页码等信息，非常便于在视图中渲染分页链接。

完整示例代码

结合上述策略，原始问题中的复杂查询可以完整转换为以下Laravel Query Builder表达式：

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', 'A.id', '=', 'T.counsel_id') // 注意这里 joinSub 的参数形式
    ->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 现在是一个 LengthAwarePaginator 实例，可以直接在 Blade 模板中渲染分页链接
// 例如：{{ $counsels->links() }}

注意事项与最佳实践

1. 何时使用 DB::raw()： DB::raw() 是一个强大的工具，但应谨慎使用。只有当Query Builder没有直接对应的方法来表达复杂的SQL函数、表达式或子句时，才考虑使用它。过度依赖 DB::raw() 会降低代码的可读性，并可能失去Query Builder提供的一些安全性和便利性。
2. 参数绑定与安全性： Query Builder会自动处理参数绑定，有效防止SQL注入攻击。即使在 DB::raw() 中，如果需要引入用户输入，也应尽量通过Query Builder的 whereRaw() 或 selectRaw() 等方法，这些方法通常支持第二个参数用于参数绑定，进一步增强安全性。例如：DB::raw('column = ?', [$value])。在本例中，$request->search_term 通过 where('A.counsel', 'like', "%{$request->search_term}%") 进行了安全的参数绑定。
3. 可读性与维护性： 转换为Query Builder的代码通常比原始SQL更具结构化，更符合Laravel的开发范式，便于团队成员理解和维护。
4. 性能考量： 尽管Query Builder抽象了SQL，但在面对极端复杂或性能敏感的查询时，理解Query Builder最终生成的SQL语句（可以使用 toSql() 方法查看）仍然很重要。必要时，可以通过添加索引、优化连接条件或调整查询逻辑来进一步提升性能。

总结

将复杂的原始SQL查询转换为Laravel Query Builder表达式是提升应用质量的重要实践。通过熟练运用 joinSub() 处理子查询、利用 DB::raw() 应对复杂聚合与条件逻辑，并结合 paginate() 实现高效分页，开发者不仅能编写出更安全、可读性更强的代码，还能充分享受Laravel生态系统带来的便利，从而专注于业务逻辑的实现。