Sum in Laravel Eloquent and Return on Page

I’m trying to get the location’s inventory value with the following eloquent query. It properly pulls up everything (location name, address, # of users, etc) except the inventory value which returns null. I first built this query in SQL which works great but for whatever reason doesn’t work in eloquent. It was the same exact query.

$locations = AppModelsLocation::select(DB::raw('locations.*', 'SUM(inventory.part_price * inventory.quantity) AS inventory_value'))
            ->leftJoin('inventory', 'locations.id', '=', 'inventory.location_id')
            ->where('locations.company_id', '=', Auth::user()->company_id)
            ->groupBy('locations.id')
            ->get();

On the blade file, I’m using a @foreach loop. I try using {{$location->inventory_value}} which returns null. I’ve tried to dump that particular variable in the controller and it is null there too. The blade file looks like this:

It should be returning the inventory value of all of the following:

Ultimately my question is how do I fix this query to get the sum of the inventory price and quantity to show on the blade file? Thanks!

Answer

When using DB::raw pass all your columns as the first parameter (within the same quote) like:

DB::raw('locations.*, SUM(inventory.part_price * inventory.quantity) AS inventory_value')

You can also use selectRaw to make it look cleaner like:

use AppModelsLocation;

$locations = Location::selectRaw('locations.*, SUM(inventory.part_price * inventory.quantity) AS inventory_value')->leftJoin...

Advertisement

Answer