How to perform SQL calculations in a Laravel Eloquent Query.

Often, you will come into circumstances where you need to append an attribute to a model, or calculate some piece of data from multiple database columns when returning data from your Laravel application. In Laravel, it is easy to make general calculations on single columns, like summing, or getting an average, but when you need to calculate data on say, 2 or more columns you may need to do some raw sql queries.

In an application I am working on, I needed to append an attribute to my Order model of "total_of_items_with_discount". This was to show the total base cost of the order items that had a discount applied to them.

First I performed an eloquent query to get the items where the "no_discount" attribute was false. Then I performed an eloquent sum method on a raw sql statement multiplying the quantity of items by the unit price.

Here is what that looked like:

// other usings
use Illuminate\Support\Facades\DB; // You need to use the DB Facade in order to use the DB::raw method.
class Order extends Model
{
public function getTotalOfItemsWithDiscountAttribute()
    {
        return $this->items()->where('no_discount', false)->sum(DB::raw('quantity * unit_price'));
    }
}

As you can see, most of the query is just a normal Eloquent query, but I am adding a raw SQL database query with the DB::raw facade.

Another way that you could potentially get your answer, is by performing a php function on each of the items, but the time complexity would be much greater than using the SQL query.