如何将含子查询的原生SQL转换为Laravel查询构造器语句？

安全阿岳

2026-9-26

Laravel 查询构造器子查询转换实现

你可以通过 Laravel 查询构造器的from()方法传入闭包的方式实现嵌套子查询，写法会自动处理参数绑定，避免直接拼接SQL引发的注入风险，实现代码如下：

// 替换为你的实际业务参数，$stores 必须为数组格式，$Limit 为分页偏移值
$stores = [...];
$Limit = 0;

$result = DB::table(function ($query) use ($stores, $Limit) {
    // 内层子查询逻辑
    $query->selectRaw('i.id AS ItemID, o.id AS OrderID, o.EmployeeID, o.created_date, (o.Quantity * i.price) AS calculation')
          ->from('stationary_orders AS o')
          ->leftJoin('stationary_items AS i', 'o.Stationary_ID', '=', 'i.id')
          ->whereIn('o.Store', $stores)
          ->orderByDesc('o.id')
          ->offset($Limit)
          ->limit(10);
}, 'inventory')
->selectRaw('EmployeeID, created_date AS OrderDate, SUM(calculation) AS TotalPrice')
->groupBy('EmployeeID')
->get();

注意事项

低版本 Laravel 如果不支持from方法直接传入闭包，可以使用手动构建子查询、合并绑定参数的写法：

$subQuery = DB::table('stationary_orders AS o')
    ->selectRaw('i.id AS ItemID, o.id AS OrderID, o.EmployeeID, o.created_date, (o.Quantity * i.price) AS calculation')
    ->leftJoin('stationary_items AS i', 'o.Stationary_ID', '=', 'i.id')
    ->whereIn('o.Store', $stores)
    ->orderByDesc('o.id')
    ->offset($Limit)
    ->limit(10);

$result = DB::table(DB::raw('(' . $subQuery->toSql() . ') AS inventory'))
    ->mergeBindings($subQuery->getQuery())
    ->selectRaw('EmployeeID, created_date AS OrderDate, SUM(calculation) AS TotalPrice')
    ->groupBy('EmployeeID')
    ->get();