如何将指定原生SQL查询转换为Laravel Eloquent或DB Facade实现？

下面是完全匹配你原生SQL逻辑的Laravel DB Facade实现代码：

use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

$today = Carbon::today();

// 子查询1：历史收货（日期早于今天）
$query1 = DB::table('Tbl_Production_RCV')
    ->select(
        'FileNo',
        'Order_No',
        'Style_No',
        'Buyer_Name',
        'Item_Code',
        '[Item_Name]',
        'BatchNo',
        'GSM',
        'Dia',
        DB::raw('SUM(Rcv_Qty) AS OpeningQty'),
        DB::raw('0 AS PreIssueQty'),
        DB::raw('0 AS PreReceivedQty'),
        DB::raw('0 AS IssueQty')
    )
    ->where('ProductType', 'Finish Fabric Production')
    ->whereDate('Prodction_Date', '<', $today)
    ->groupBy(
        'FileNo',
        'Order_No',
        'Style_No',
        'Buyer_Name',
        'Item_Code',
        '[Item_Name]',
        'BatchNo',
        'GSM',
        'Dia'
    );

// 子查询2：今日发货
$query2 = DB::table('Tbl_Production_IS')
    ->select(
        'FileNo',
        'Order_No',
        'Style_No',
        'Buyer_Name',
        'Item_Code',
        '[Item_Name]',
        'BatchNo',
        'GSM',
        'Dia',
        DB::raw('0 AS OpeningQty'),
        DB::raw('SUM(Rcv_Qty) AS PreIssueQty'),
        DB::raw('0 AS PreReceivedQty'),
        DB::raw('0 AS IssueQty')
    )
    ->where('ProductType', 'Finish Fabric Production')
    ->whereDate('Prodction_Date', '=', $today)
    ->groupBy(
        'FileNo',
        'Order_No',
        'Style_No',
        'Buyer_Name',
        'Item_Code',
        '[Item_Name]',
        'BatchNo',
        'GSM',
        'Dia'
    );

// 子查询3：今日收货
$query3 = DB::table('Tbl_Production_RCV')
    ->select(
        'FileNo',
        'Order_No',
        'Style_No',
        'Buyer_Name',
        'Item_Code',
        '[Item_Name]',
        'BatchNo',
        'GSM',
        'Dia',
        DB::raw('0 AS OpeningQty'),
        DB::raw('0 AS PreIssueQty'),
        DB::raw('SUM(Rcv_Qty) AS PreReceivedQty'),
        DB::raw('0 AS IssueQty')
    )
    ->where('ProductType', 'Finish Fabric Production')
    ->whereDate('Prodction_Date', '=', $today)
    ->groupBy(
        'FileNo',
        'Order_No',
        'Style_No',
        'Buyer_Name',
        'Item_Code',
        '[Item_Name]',
        'BatchNo',
        'GSM',
        'Dia'
    );

// 子查询4：历史发货
$query4 = DB::table('Tbl_Production_IS')
    ->select(
        'FileNo',
        'Order_No',
        'Style_No',
        'Buyer_Name',
        'Item_Code',
        '[Item_Name]',
        'BatchNo',
        'GSM',
        'Dia',
        DB::raw('0 AS OpeningQty'),
        DB::raw('0 AS PreIssueQty'),
        DB::raw('0 AS PreReceivedQty'),
        DB::raw('SUM(Rcv_Qty) AS IssueQty')
    )
    ->where('ProductType', 'Finish Fabric Production')
    ->whereDate('Prodction_Date', '<', $today)
    ->groupBy(
        'FileNo',
        'Order_No',
        'Style_No',
        'Buyer_Name',
        'Item_Code',
        '[Item_Name]',
        'BatchNo',
        'GSM',
        'Dia'
    );

// 合并子查询并执行外层聚合与过滤
$result = DB::table(
    DB::table(
        $query1->unionAll($query2)->unionAll($query3)->unionAll($query4),
        'y'
    )
    ->select(
        'FileNo',
        'Order_No',
        'Style_No',
        'Buyer_Name',
        'Item_Code',
        '[Item_Name]',
        'BatchNo',
        'GSM',
        'Dia',
        DB::raw('(SUM(OpeningQty) - SUM(IssueQty)) AS fOpeningQty'),
        DB::raw('SUM(PreReceivedQty) AS fReceivedQty'),
        DB::raw('SUM(PreIssueQty) AS fIssueQty'),
        DB::raw('(SUM(OpeningQty) - SUM(IssueQty) - SUM(PreIssueQty) + SUM(PreReceivedQty)) AS fClosingQty')
    )
    ->groupBy(
        'FileNo',
        'Order_No',
        'Style_No',
        'Buyer_Name',
        'Item_Code',
        '[Item_Name]',
        'BatchNo',
        'GSM',
        'Dia'
    ),
    'z'
)
->where(function ($query) {
    $query->where('fOpeningQty', '>', 0)
          ->orWhere('fReceivedQty', '>', 0)
          ->orWhere('fIssueQty', '>', 0)
          ->orWhere('fClosingQty', '>', 0);
})
->get();

核心匹配点 #

• 用Carbon::today()替代原生SQL的GETDATE()，贴合Laravel日期处理习惯
• 用unionAll替代UNION，因四个子查询无重复数据，性能更优
• 聚合计算、分组规则、过滤条件完全复刻原生SQL，确保结果一致
• 嵌套查询结构与原SQL一一对应，逻辑清晰易维护

内容的提问来源于stack exchange，提问作者Anis