如何将原始SQL子查询转换为Laravel查询构建器表达式

将原始SQL子查询转换为Laravel查询构建器表达式，核心是通过fromSub方法实现嵌套查询的链式构建。以下是具体步骤和代码示例：

1. 分析原始SQL结构

原始SQL包含一个子查询作为FROM子句的临时表（inventory），其功能为：

从stationary_orders和stationary_items表中关联查询订单明细。
计算每项价值（Quantity * price）并筛选商店范围。
外部查询按员工ID分组汇总总价。

2. 构建内部子查询

使用Laravel查询构建器逐步实现子查询逻辑：

use IlluminateSupportFacadesDB;$stores = [1, 2, 3]; // 示例商店ID数组$limit = 0; // 示例偏移量$pageSize = 10; // 示例每页数量$nestedQuery = DB::table('stationary_orders AS o') ->select([ 'i.id AS ItemID', 'o.id AS OrderID', 'o.EmployeeID', 'o.created_date', DB::raw('(o.Quantity * i.price) AS calculation') // 计算字段 ]) ->leftJoin('stationary_items AS i', 'o.Stationary_ID', '=', 'i.id') ->whereIn('o.Store', $stores) // 安全处理IN条件 ->orderBy('o.id', 'DESC') ->offset($limit) ->limit($pageSize);

关键点：

使用DB::raw()处理计算字段，避免SQL注入。
通过whereIn替代WHERE ... IN，自动参数绑定。
明确表别名（o和i）确保后续引用无误。

3. 将子查询嵌入主查询

通过fromSub将子查询作为临时表，并完成外部查询：

$result = DB::query() ->fromSub($nestedQuery, 'inventory') // 子查询别名 ->select([ 'inventory.EmployeeID', 'inventory.created_date AS OrderDate', DB::raw('SUM(inventory.calculation) AS TotalPrice') // 聚合函数 ]) ->groupBy('inventory.EmployeeID') ->get(); // 返回结果集

关键点：

fromSub第一个参数为子查询构建器，第二个参数为临时表别名。
外部查询的SELECT和GROUP BY需与子查询别名（inventory）一致。
使用DB::raw()处理聚合函数SUM()。

4. 完整控制器示例

将上述逻辑整合到控制器方法中：

namespace AppHttpControllers;use IlluminateHttpRequest;use IlluminateSupportFacadesDB;class OrderController extends Controller{ public function getEmployeeOrderSummary(Request $request) { // 从请求获取参数（带默认值） $stores = $request->input('stores', [1, 2, 3]); $limit = $request->input('offset', 0); $pageSize = $request->input('limit', 10); // 1. 构建子查询 $nestedQuery = DB::table('stationary_orders AS o') ->select([ 'i.id AS ItemID', 'o.id AS OrderID', 'o.EmployeeID', 'o.created_date', DB::raw('(o.Quantity * i.price) AS calculation') ]) ->leftJoin('stationary_items AS i', 'o.Stationary_ID', '=', 'i.id') ->whereIn('o.Store', $stores) ->orderBy('o.id', 'DESC') ->offset($limit) ->limit($pageSize); // 2. 构建主查询 $employeeOrderSummary = DB::query() ->fromSub($nestedQuery, 'inventory') ->select([ 'inventory.EmployeeID', 'inventory.created_date AS OrderDate', DB::raw('SUM(inventory.calculation) AS TotalPrice') ]) ->groupBy('inventory.EmployeeID') ->get(); return response()->json($employeeOrderSummary); }}5. 注意事项与优化建议

别名一致性：确保子查询和主查询中的别名（如inventory）与字段引用完全匹配。
安全性：避免在DB::raw()中直接拼接用户输入，使用参数绑定（如whereIn）。
调试技巧：
使用->toSql()查看生成的SQL语句。
使用->dump()（Laravel 8+）打印查询及绑定参数。
性能优化：
为stationary_orders.Store和Stationary_ID字段添加索引。
复杂查询可拆分为多个简单查询或使用缓存。

6. 总结

通过fromSub方法，Laravel查询构建器能够优雅地处理嵌套查询，兼顾安全性（参数绑定）、可读性（链式调用）和跨数据库兼容性。分解问题为子查询和主查询两步构建，可显著降低复杂查询的开发难度。

您可能感兴趣问答

Collapsible

热门标签

热点问答