Laravel中如何将原生查询的计算结果用于后续原生查询计算？

Hey there, I’ve dealt with this exact problem before—trying to reference a calculated alias from the same SELECT clause just doesn’t work in SQL, and that’s why your current code is failing. Let me break down why and show you a few solid solutions.

Why Your Current Code Fails #

SQL evaluates all expressions in the SELECT clause simultaneously. By the time it tries to calculate netamount using grossamount, the database hasn’t yet resolved that alias (it doesn’t exist in the scope of the same SELECT). So we need to work around this SQL limitation.

Solution 1: Repeat the Calculation (Simple & Direct) #

For straightforward calculations like yours, the easiest fix is to repeat the logic for grossamount inside the netamount calculation. It’s a bit redundant, but it’s quick and works perfectly for most cases:

query()->select([
    'order_items.id',
    DB::raw("(order_qty * mp_price) as grossamount"),
    'order_items.discount_amount',
    DB::raw("(order_qty * mp_price - discount_amount) as netamount"),
]);

Solution 2: Use a Subquery or CTE (Cleaner for Complex Logic) #

If your calculation gets more complicated (and repeating code becomes messy), use a subquery or Common Table Expression (CTE) to first compute grossamount, then reference it in the outer query.

Subquery Approach

query()->fromSub(function ($subQuery) {
    $subQuery->from('order_items')
             ->select([
                 'id',
                 DB::raw("(order_qty * mp_price) as grossamount"),
                 'discount_amount',
             ]);
}, 'oi')
->select([
    'oi.id',
    'oi.grossamount',
    'oi.discount_amount',
    DB::raw("(oi.grossamount - oi.discount_amount) as netamount"),
]);

CTE Approach (Laravel 8+)

CTEs make the query more readable, especially if you have multiple layers of calculations:

query()->withExpression('order_items_with_gross', function ($cte) {
    $cte->from('order_items')
        ->select([
            'id',
            DB::raw("(order_qty * mp_price) as grossamount"),
            'discount_amount',
        ]);
})
->select([
    'order_items_with_gross.id',
    'order_items_with_gross.grossamount',
    'order_items_with_gross.discount_amount',
    DB::raw("(order_items_with_gross.grossamount - order_items_with_gross.discount_amount) as netamount"),
])
->from('order_items_with_gross');

Solution 3: Use an Eloquent Accessor (For Post-Query Calculation) #

If you don’t need to use netamount in database-level operations (like filtering, sorting, or grouping), you can calculate it in your model using an accessor. This happens in memory after fetching the data:

First, add the accessor to your OrderItem model:

class OrderItem extends Model
{
    // ...

    public function getNetamountAttribute()
    {
        return $this->grossamount - $this->discount_amount;
    }
}

Then your query only needs to fetch the necessary fields:

query()->select([
    'order_items.id',
    DB::raw("(order_qty * mp_price) as grossamount"),
    'order_items.discount_amount',
]);

You can then access the calculated value directly on your model instances:

$orderItem->netamount; // Automatically computes grossamount - discount_amount

Which Solution Should You Choose? #

• Repeat the calculation: Best for simple, one-off calculations where redundancy isn’t a problem.
• Subquery/CTE: Ideal for complex logic where you want to avoid duplicating code, or if you need to use the calculated alias in other parts of the query (like WHERE or ORDER BY).
• Accessor: Perfect if you only need the calculated value after fetching the records, and don’t need to interact with it at the database level.

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