如何将指定SQL语句转换为Laravel查询构造器语句？

Hey Emma! Converting that raw SQL into Laravel's Query Builder is straightforward—we can map each subquery and selection directly using DB::raw() to keep the logic identical to your original query. Here's the equivalent code:

use Illuminate\Support\Facades\DB;

$products = DB::table('products')
    ->select([
        'products.id',
        'products.name',
        DB::raw('(SELECT IFNULL(SUM(stocks.qty), 0) FROM stocks WHERE stocks.pid = products.id AND DATE(stocks.created_at) = CURDATE()) AS stock_in_today'),
        DB::raw('(SELECT IFNULL(SUM(loadings.qty), 0) FROM loadings WHERE loadings.pid = products.id AND DATE(loadings.created_at) = CURDATE()) AS total_loadings_today'),
        DB::raw('(SELECT IFNULL(SUM(stocks.qty), 0) FROM stocks WHERE stocks.pid = products.id) AS total_stock_till_date'),
        DB::raw('(SELECT IFNULL(SUM(loadings.qty), 0) FROM loadings WHERE loadings.pid = products.id) AS total_loadings_till_date'),
        DB::raw('(
            (SELECT IFNULL(SUM(stocks.qty), 0) FROM stocks WHERE stocks.pid = products.id AND DATE(stocks.created_at) < CURDATE()) -
            (SELECT IFNULL(SUM(loadings.qty), 0) FROM loadings WHERE loadings.pid = products.id AND DATE(loadings.created_at) < CURDATE())
        ) AS opening_balance'),
        DB::raw('(
            (SELECT IFNULL(SUM(stocks.qty), 0) FROM stocks WHERE stocks.pid = products.id) -
            (SELECT IFNULL(SUM(loadings.qty), 0) FROM loadings WHERE loadings.pid = products.id)
        ) AS closing_balance')
    ])
    ->get();

This query mirrors your original SQL exactly: it selects the product ID/name, then calculates each aggregated value via subqueries, using IFNULL to fall back to 0 when there are no matching records in stocks or loadings.

If you're working with a Product Eloquent model, you can simplify it a bit by starting from the model instead of DB::table():

use App\Models\Product;

$products = Product::select([
    'id',
    'name',
    DB::raw('(SELECT IFNULL(SUM(stocks.qty), 0) FROM stocks WHERE stocks.pid = products.id AND DATE(stocks.created_at) = CURDATE()) AS stock_in_today'),
    DB::raw('(SELECT IFNULL(SUM(loadings.qty), 0) FROM loadings WHERE loadings.pid = products.id AND DATE(loadings.created_at) = CURDATE()) AS total_loadings_today'),
    DB::raw('(SELECT IFNULL(SUM(stocks.qty), 0) FROM stocks WHERE stocks.pid = products.id) AS total_stock_till_date'),
    DB::raw('(SELECT IFNULL(SUM(loadings.qty), 0) FROM loadings WHERE loadings.pid = products.id) AS total_loadings_till_date'),
    DB::raw('(
        (SELECT IFNULL(SUM(stocks.qty), 0) FROM stocks WHERE stocks.pid = products.id AND DATE(stocks.created_at) < CURDATE()) -
        (SELECT IFNULL(SUM(loadings.qty), 0) FROM loadings WHERE loadings.pid = products.id AND DATE(loadings.created_at) < CURDATE())
    ) AS opening_balance'),
    DB::raw('(
        (SELECT IFNULL(SUM(stocks.qty), 0) FROM stocks WHERE stocks.pid = products.id) -
        (SELECT IFNULL(SUM(loadings.qty), 0) FROM loadings WHERE loadings.pid = products.id)
    ) AS closing_balance')
])->get();

Performance Optimization Note #

Your original query uses multiple repeated subqueries, which can be inefficient for large datasets. A better approach is to pre-aggregate the data using subqueries or joins to avoid recalculating sums multiple times. Here's an optimized version using leftJoinSub:

use Illuminate\Support\Facades\DB;

$products = DB::table('products')
    // Pre-aggregate total stock till date
    ->leftJoinSub(
        DB::table('stocks')
            ->select('pid', DB::raw('SUM(qty) AS total'))
            ->groupBy('pid'),
        'total_stocks',
        'total_stocks.pid',
        '=',
        'products.id'
    )
    // Pre-aggregate today's stock
    ->leftJoinSub(
        DB::table('stocks')
            ->select('pid', DB::raw('SUM(qty) AS today'))
            ->whereDate('created_at', today())
            ->groupBy('pid'),
        'today_stocks',
        'today_stocks.pid',
        '=',
        'products.id'
    )
    // Pre-aggregate total loadings till date
    ->leftJoinSub(
        DB::table('loadings')
            ->select('pid', DB::raw('SUM(qty) AS total'))
            ->groupBy('pid'),
        'total_loadings',
        'total_loadings.pid',
        '=',
        'products.id'
    )
    // Pre-aggregate today's loadings
    ->leftJoinSub(
        DB::table('loadings')
            ->select('pid', DB::raw('SUM(qty) AS today'))
            ->whereDate('created_at', today())
            ->groupBy('pid'),
        'today_loadings',
        'today_loadings.pid',
        '=',
        'products.id'
    )
    // Calculate all required fields from pre-aggregated data
    ->select([
        'products.id',
        'products.name',
        DB::raw('IFNULL(today_stocks.today, 0) AS stock_in_today'),
        DB::raw('IFNULL(today_loadings.today, 0) AS total_loadings_today'),
        DB::raw('IFNULL(total_stocks.total, 0) AS total_stock_till_date'),
        DB::raw('IFNULL(total_loadings.total, 0) AS total_loadings_till_date'),
        DB::raw('(IFNULL(total_stocks.total, 0) - IFNULL(today_stocks.today, 0)) - (IFNULL(total_loadings.total, 0) - IFNULL(today_loadings.today, 0)) AS opening_balance'),
        DB::raw('IFNULL(total_stocks.total, 0) - IFNULL(total_loadings.total, 0) AS closing_balance')
    ])
    ->get();

This version runs fewer aggregate queries overall, which will be faster if you're dealing with lots of records in stocks or loadings.

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