如何将复杂原始SQL查询转换为Laravel查询构建器

Laravel查询构建器的优势

在Laravel应用开发中，数据库交互是核心部分。尽管直接编写原始SQL语句可以实现任何复杂的查询，但Laravel的查询构建器提供了诸多优势，使其成为更优的选择：

• 可读性与维护性： 链式调用使查询逻辑清晰，易于理解和维护。
• 安全性： 自动防止SQL注入攻击，无需手动处理参数绑定。
• 跨数据库兼容性： 构建器生成的SQL会根据所使用的数据库类型进行适配。
• 丰富的功能： 内置了分页、软删除、预加载等高级功能，大大简化开发。

对于需要分页、数据量庞大的查询场景，使用查询构建器尤为重要，因为它能轻松集成paginate()等方法，而原始SQL则需要手动实现分页逻辑，复杂且易错。

复杂SQL查询的转换策略

将复杂的原始SQL查询转换为Laravel查询构建器，核心在于理解如何将SQL中的特定构造映射到构建器的方法。主要策略包括：

1. 子查询的转换： 对于需要在主查询中作为表使用的子查询，可以使用joinSub()方法。
2. 聚合函数与复杂表达式： 对于MIN(), COUNT(), SUM()等聚合函数，以及IF()等条件表达式，当查询构建器没有直接对应的方法时，可使用DB::raw()来嵌入原生SQL片段。
3. 条件与分组： where()和groupBy()方法直接对应SQL的WHERE和GROUP BY子句。
4. 分页： 转换为查询构建器后，可以直接链式调用paginate()方法实现分页。

实战案例：将复杂SQL转换为Query Builder

假设我们有一个复杂的原始SQL查询，它涉及到子查询、多重聚合以及条件逻辑，目标是获取律师（counsel）的案件统计信息，并支持分页。

以下是转换后的Laravel查询构建器代码示例，它有效地实现了这一目标：

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

// 模拟请求对象，实际应用中会通过依赖注入获取
$request = new Request(['search_term' => 'John Doe']);

// 步骤一：处理子查询 (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', 'A') // 使用别名A
    ->select([
        'A.enrolment_number as id',
        DB::raw('MIN(A.counsel) as counsel'), // 使用DB::raw处理MIN聚合函数
    ])
    ->groupBy('enrolment_number');

// 步骤二：主查询与子查询联接 (counsels)
// 原始SQL中的主查询部分，联接上述子查询：
// SELECT T.counsel_id, A.counsel, COUNT(T.counsel_id) as total, ...
// FROM cp_cases_counsel as T
// JOIN (<subquery>) as A ON A.id = T.counsel_id
$counsels = DB::table('cp_cases_counsel', 'T') // 使用别名T
    ->joinSub($cpCounsel, 'A', function ($join) { // 使用joinSub联接子查询，并指定子查询的别名A
        $join->on('A.id', '=', 'T.counsel_id'); // 定义联接条件
    })
    ->where('A.counsel', 'like', "%{$request->search_term}%") // 添加WHERE条件
    ->select([
        'T.counsel_id',
        'A.counsel',
        DB::raw('COUNT(T.counsel_id) as total'), // 总案件数
        // 步骤三：处理复杂的选择列与条件聚合
        // 以下均使用DB::raw处理SUM(IF(...))形式的条件聚合
        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); // 步骤五：实现分页，每页15条记录

// $counsels 现在是一个Paginator实例，可以直接在视图中使用
// 例如：$counsels->links() 生成分页链接

代码解析：

蓝心千询

蓝心千询是vivo推出的一个多功能AI智能助手

34

查看详情

1. 子查询构建 ($cpCounsel)：

   • DB::table('cp_counsel', 'A'): 初始化对cp_counsel表的查询，并指定别名为A。
   • select(['A.enrolment_number as id', DB::raw('MIN(A.counsel) as counsel')]): 选择所需的列。MIN(A.counsel)是一个聚合函数，由于Query Builder没有直接的minSelect方法来指定别名，或者为了更灵活地处理SQL函数，我们使用DB::raw()来包裹这个原生SQL片段。
   • groupBy('enrolment_number'): 对enrolment_number进行分组。

2. 主查询与子查询联接 ($counsels)：

   • DB::table('cp_cases_counsel', 'T'): 初始化对主表cp_cases_counsel的查询，别名为T。
   • joinSub($cpCounsel, 'A', function ($join) { ... }): 这是将子查询联接到主查询的关键。
     • $cpCounsel：传入之前构建的子查询实例。
     • 'A'：指定子查询在主查询中的别名。
     • function ($join) { $join->on('A.id', '=', 'T.counsel_id'); }: 定义联接条件，即子查询的id列与主表的counsel_id列相等。
   • where('A.counsel', 'like', "%{$request->search_term}%"): 添加一个WHERE条件，根据搜索词过滤律师名称。
   • select([...]): 选择主查询所需的列。这里的亮点是大量使用了DB::raw()来处理复杂的条件聚合，例如SUM(if(T.court_id = 2, 1, 0))，这在原始SQL中很常见，用于根据条件进行计数或求和。
   • groupBy('T.counsel_id', 'A.counsel'): 对主查询结果进行分组。
   • paginate(15): 这是将原始SQL转换为Query Builder的最大优势之一。它自动处理了SQL的LIMIT和OFFSET，并返回一个LengthAwarePaginator实例，包含了所有分页信息，方便在视图中渲染分页链接。

注意事项

• 何时使用DB::raw()： 尽可能使用查询构建器提供的具体方法。只有当构建器没有直接对应的方法来表达复杂的SQL函数、表达式或子句时（例如上述的MIN()或SUM(IF(...))），才应考虑使用DB::raw()。过度使用DB::raw()会降低查询构建器的优势（如数据库兼容性检查和参数绑定）。
• 性能考量： 即使使用了查询构建器，复杂查询的性能仍然取决于数据库索引、数据量和查询本身的效率。在开发过程中，应使用Laravel Debugbar或数据库的慢查询日志来监控和优化查询性能。
• 调试查询： 如果需要查看查询构建器生成的实际SQL语句，可以使用toSql()方法：

  $counsels->toSql();
  // 配合 getBindings() 可以查看绑定的参数
  $counsels->getBindings();

  登录后复制

  这对于调试复杂的查询逻辑非常有用。

总结

将原始SQL查询转换为Laravel查询构建器是一个推荐的最佳实践，尤其对于需要分页和维护的复杂查询。通过巧妙地结合DB::raw()处理原生SQL片段和joinSub()管理子查询，我们可以构建出既强大又易于管理的代码。这不仅提升了代码的可读性和安全性，还使得利用Laravel生态系统中的高级功能（如分页）变得轻而易举，从而显著提高开发效率。

以上就是如何将复杂原始SQL查询转换为Laravel查询构建器的详细内容，更多请关注php中文网其它相关文章！