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

SELECT inventory.EmployeeID,
       inventory.created_date AS OrderDate,
       SUM(inventory.calculation) AS TotalPrice
FROM (
    SELECT i.id AS ItemID,
           o.id AS OrderID,
           o.EmployeeID,
           o.created_date,
           (o.Quantity * i.price) AS calculation
    FROM `stationary_orders` AS o
    LEFT JOIN `stationary_items` AS i ON o.Stationary_ID = i.id
    WHERE o.Store IN $storess
    ORDER BY o.id DESC
    LIMIT $Limit,10
) AS inventory
GROUP BY inventory.EmployeeID;

use Illuminate\Support\Facades\DB;

// 假设 $stores 和 $limitOffset 是从外部传入的变量
$stores = [1, 2, 3]; // 示例商店ID数组
$limit = 0; // 示例偏移量
$pageSize = 10; // 示例每页数量

$nestedQuery = DB::table('stationary_orders', 'o') // 使用 'o' 作为 orders 表的别名
    ->select(
        'i.id AS ItemID',
        'o.id AS OrderID',
        'o.EmployeeID',
        'o.created_date',
        DB::raw('(o.Quantity * i.price) AS calculation') // 计算字段使用 DB::raw
    )
    ->leftJoin('stationary_items AS i', 'o.Stationary_ID', '=', 'i.id') // 使用 'i' 作为 items 表的别名
    ->whereIn('o.Store', $stores) // 使用 whereIn 处理 IN 子句
    ->orderBy('o.id', 'DESC')
    ->offset($limit) // 对应 LIMIT $Limit
    ->limit($pageSize); // 对应 ,10

$result = DB::query() // 可以使用 DB::query() 或 DB::table('') 开始一个新查询
    ->fromSub($nestedQuery, 'inventory') // 将 $nestedQuery 作为子查询，并命名为 'inventory'
    ->select(
        'inventory.EmployeeID',
        'inventory.created_date AS OrderDate',
        DB::raw('SUM(inventory.calculation) AS TotalPrice') // 再次使用 DB::raw() 处理聚合函数
    )
    ->groupBy('inventory.EmployeeID')
    ->get(); // 执行查询并获取结果

// $result 将是一个 Illuminate\Support\Collection 对象，其中包含查询结果

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class OrderController extends Controller
{
    public function getEmployeeOrderSummary(Request $request)
    {
        // 从请求中获取或设置默认值
        $stores = $request->input('stores', [1, 2, 3]); // 示例：从请求中获取商店ID数组
        $limit = $request->input('offset', 0); // 示例：从请求中获取偏移量
        $pageSize = $request->input('limit', 10); // 示例：从请求中获取每页数量

        // 1. 构建内部子查询
        $nestedQuery = DB::table('stationary_orders', '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);
    }
}