Possible to make laravel eloquent query or DB facade which i get result from raw sql query?

Question

SELECT * FROM (
SELECT FileNo, Order_No, Style_No, Buyer_Name, Item_Code, [Item_Name], BatchNo, GSM, Dia,
(SUM(OpeningQty) - SUM(IssueQty)) AS fOpeningQty, SUM(PreReceivedQty) AS fReceivedQty, 
SUM(PreIssueQty) AS fIssueQty, 
(SUM(OpeningQty) - SUM(IssueQty) - SUM(PreIssueQty) + SUM(PreReceivedQty)) AS fClosingQty
FROM (
    SELECT FileNo, Order_No, Style_No, Buyer_Name, Item_Code, [Item_Name], BatchNo, GSM, Dia,
    SUM(Rcv_Qty) AS OpeningQty, 0 AS 'PreIssueQty', 0 AS 'PreReceivedQty', 0 AS 'IssueQty'
    FROM Tbl_Production_RCV
    WHERE ProductType='Finish Fabric Production' 
    AND CONVERT(date, Prodction_Date) < convert(date, GETDATE())
    GROUP BY FileNo, Order_No, Style_No, Buyer_Name, Item_Code, [Item_Name], BatchNo, GSM, Dia
    
    UNION 
    
    SELECT FileNo, Order_No, Style_No, Buyer_Name, Item_Code, [Item_Name], BatchNo, GSM, Dia,
    0 AS OpeningQty, SUM(Rcv_Qty) AS 'PreIssueQty', 0 AS 'PreReceivedQty', 0 AS 'IssueQty'
    FROM Tbl_Production_IS
    WHERE ProductType='Finish Fabric Production' 
    AND CONVERT(date, Prodction_Date) BETWEEN convert(date, GETDATE()) AND convert(date, GETDATE())
    GROUP BY FileNo, Order_No, Style_No, Buyer_Name, Item_Code, [Item_Name], BatchNo, GSM, Dia
    
    UNION 
    
    SELECT FileNo, Order_No, Style_No, Buyer_Name, Item_Code, [Item_Name], BatchNo, GSM, Dia,
    0 AS OpeningQty, 0 AS 'PreIssueQty', SUM(Rcv_Qty) AS 'PreReceivedQty', 0 AS 'IssueQty'
    FROM Tbl_Production_RCV
    WHERE ProductType='Finish Fabric Production' 
    AND CONVERT(date, Prodction_Date) BETWEEN convert(date, GETDATE()) AND convert(date, GETDATE())
    GROUP BY FileNo, Order_No, Style_No, Buyer_Name, Item_Code, [Item_Name], BatchNo, GSM, Dia
    
    UNION 
    
    SELECT FileNo, Order_No, Style_No, Buyer_Name, Item_Code, [Item_Name], BatchNo, GSM, Dia,
    0 AS OpeningQty, 0 AS 'PreIssueQty', 0 AS 'PreReceivedQty', SUM(Rcv_Qty) AS 'IssueQty'
    FROM Tbl_Production_IS
    WHERE ProductType='Finish Fabric Production' 
    AND CONVERT(date, Prodction_Date) < convert(date, GETDATE())
    GROUP BY FileNo, Order_No, Style_No, Buyer_Name, Item_Code, [Item_Name], BatchNo, GSM, Dia
) y
GROUP BY  FileNo, Order_No, Style_No, Buyer_Name, Item_Code, [Item_Name], BatchNo, GSM, 
Dia
) z
WHERE fOpeningQty > 0 OR fReceivedQty > 0 OR fIssueQty > 0 OR fClosingQty > 0

This is my raw sql query and I get result what i want. I am begginer level developer. I search all the web for this question but never found any expected answer. Anybody can help me how can i get my expected result by laravel eloquent or DB facade. Thanks in advance.

Answer 1

Here’s the updated version of your query:

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

$today = Carbon::now();

$subquery = DB::table('Tbl_Production_RCV')
    ->selectRaw('FileNo, Order_No, Style_No, Buyer_Name, Item_Code, Item_Name, BatchNo, GSM, Dia,
                 SUM(Rcv_Qty) AS OpeningQty, 0 AS PreIssueQty, 0 AS PreReceivedQty, 0 AS IssueQty')
    ->where('ProductType', 'Finish Fabric Production')
    ->where('Prodction_Date', '<', $today->toDateString())
    ->groupBy('FileNo', 'Order_No', 'Style_No', 'Buyer_Name', 'Item_Code', 'Item_Name', 'BatchNo', 'GSM', 'Dia')
    ->unionAll(
        DB::table('Tbl_Production_IS')
            ->selectRaw('FileNo, Order_No, Style_No, Buyer_Name, Item_Code, Item_Name, BatchNo, GSM, Dia,
                         0 AS OpeningQty, SUM(Rcv_Qty) AS PreIssueQty, 0 AS PreReceivedQty, 0 AS IssueQty')
            ->where('ProductType', 'Finish Fabric Production')
            ->whereBetween('Prodction_Date', [$today->toDateString(), $today->toDateString()])
            ->groupBy('FileNo', 'Order_No', 'Style_No', 'Buyer_Name', 'Item_Code', 'Item_Name', 'BatchNo', 'GSM', 'Dia')
    )
    ->unionAll(
        DB::table('Tbl_Production_RCV')
            ->selectRaw('FileNo, Order_No, Style_No, Buyer_Name, Item_Code, Item_Name, BatchNo, GSM, Dia,
                         0 AS OpeningQty, 0 AS PreIssueQty, SUM(Rcv_Qty) AS PreReceivedQty, 0 AS IssueQty')
            ->where('ProductType', 'Finish Fabric Production')
            ->whereBetween('Prodction_Date', [$today->toDateString(), $today->toDateString()])
            ->groupBy('FileNo', 'Order_No', 'Style_No', 'Buyer_Name', 'Item_Code', 'Item_Name', 'BatchNo', 'GSM', 'Dia')
    )
    ->unionAll(
        DB::table('Tbl_Production_IS')
            ->selectRaw('FileNo, Order_No, Style_No, Buyer_Name, Item_Code, Item_Name, BatchNo, GSM, Dia,
                         0 AS OpeningQty, 0 AS PreIssueQty, 0 AS PreReceivedQty, SUM(Rcv_Qty) AS IssueQty')
            ->where('ProductType', 'Finish Fabric Production')
            ->where('Prodction_Date', '<', $today->toDateString())
            ->groupBy('FileNo', 'Order_No', 'Style_No', 'Buyer_Name', 'Item_Code', 'Item_Name', 'BatchNo', 'GSM', 'Dia')
    );

// Now, wrapping this subquery to perform final calculations
$finalQuery = DB::table(DB::raw("({$subquery->toRawSql()}) as sub"))
    ->selectRaw('FileNo, Order_No, Style_No, Buyer_Name, Item_Code, Item_Name, BatchNo, GSM, Dia,
                 SUM(OpeningQty) - SUM(IssueQty) AS fOpeningQty, SUM(PreReceivedQty) AS fReceivedQty, SUM(PreIssueQty) AS fIssueQty,
                 (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')
    ->havingRaw('fOpeningQty > 0 OR fReceivedQty > 0 OR fIssueQty > 0 OR fClosingQty > 0')
    ->get();

$results = $finalQuery;