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

2025-08-13

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

如何将复杂原始sql查询转换为laravel查询构建器

本教程详细阐述了如何将包含子查询、复杂聚合函数及条件逻辑的原始SQL语句优雅地转换为Laravel查询构建器（Query Builder）操作。通过利用DB::raw()处理原生SQL片段和joinSub()实现子查询联接，文章展示了如何构建可读性更强、更安全且易于分页的数据库查询，从而提升开发效率和代码质量。

Laravel查询构建器的优势

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

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

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

复杂SQL查询的转换策略

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

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

登录后复制

代码解析：

子查询构建 ($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进行分组。
主查询与子查询联接 ($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中文网其它相关文章！

https://www.php.cn/faq/1452363.html