Laravel：将原生 SQL 查询转换为 Query Builder

本文档旨在指导开发者如何将现有的原生 SQL 查询语句转换为 Laravel 的 Query Builder 形式，以便更好地利用 Laravel 提供的诸如分页等功能。通过示例代码，详细展示了如何使用 Query Builder 实现复杂的查询逻辑，并提供了一些注意事项，帮助开发者在实际项目中高效地进行转换。

在 Laravel 开发中，Query Builder 提供了一种更加安全、便捷的方式来构建数据库查询。当需要将现有的原生 SQL 查询迁移到 Laravel 项目时，将其转换为 Query Builder 是一个常见的需求。以下是如何将原生 SQL 查询转换为 Laravel Query Builder 的步骤和注意事项。

基本转换原则

通常，SQL 语句中的各个部分都可以在 Laravel Query Builder 中找到对应的实现。关键在于理解 SQL 语句的结构，并找到 Query Builder 中与之等价的方法。对于一些特殊的函数或操作，可以使用 DB::raw() 来执行原生的 SQL 代码。

示例：转换复杂的 SQL 查询

假设有如下原生 SQL 查询（已在问题中给出）：

$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.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);

这个查询包含子查询、JOIN、WHERE 条件、聚合函数以及分页。

逐步转换

1. 子查询 (Subquery)

   在 Laravel 中，可以使用 joinSub() 方法来执行子查询。

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

2. JOIN

   使用 joinSub() 将子查询连接到主查询。

   ->joinSub($cpCounsel, 'A.id', '=', 'T.counsel_id')

3. WHERE 条件

   使用 where() 方法添加 WHERE 条件。

   ->where('A.counsel', 'like', "%$request->search_term%")

4. SELECT 字段

   使用 select() 方法选择需要的字段。对于需要使用 SQL 函数的情况，使用 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('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'), ])

5. GROUP BY

   使用 groupBy() 方法进行分组。

   ->groupBy('T.counsel_id', 'A.counsel')

6. 分页

   使用 paginate() 方法进行分页。

   ->paginate(15);

完整代码

将以上步骤组合起来，完整的 Query Builder 代码如下：

$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.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);

注意事项

• SQL 函数： 对于复杂的 SQL 函数，如 SUM(if())，仍然需要使用 DB::raw() 来执行。
• 别名： 在使用 select() 方法时，可以通过 ‘column as alias’ 的方式来定义别名。
• 安全性： Query Builder 提供了参数绑定的机制，可以有效防止 SQL 注入。尽量避免直接拼接字符串到查询语句中。
• 可读性： 尽量保持 Query Builder 代码的清晰和可读性，方便后续维护。
• 性能： 复杂的查询可能会影响性能。在实际应用中，需要根据具体情况进行优化。

总结

将原生 SQL 查询转换为 Laravel Query Builder 可以提高代码的可维护性和安全性，并能更好地利用 Laravel 提供的功能。虽然转换过程可能需要一些时间和精力，但最终会带来更好的开发体验。通过理解 SQL 语句的结构，并找到 Query Builder 中与之等价的方法，可以有效地完成转换工作。记住，对于一些特殊的函数或操作，可以使用 DB::raw() 来执行原生的 SQL 代码。