How to get the eloquent value

Hi, i have a question here

select wh.code, wh.name, loc.location_type_id, s.name, count(distinct loc.id) as total_location, (select count(distinct stk.location_id ) from stock_balances stk where stk.warehouse_id = wh.id and stk.inventory_status ='20') as total_occupied from warehouses wh, warehouse_locations loc, settings s where wh.id = loc.warehouse_id and loc.location_type_id = s.id group by wh.code, wh.name , wh.id, loc.location_type_id;

I have sql code here how can i use this code in laravel ? can someone help me?

LaryAI

3 years ago

Level 58

To use this SQL code in Laravel, you can create a model for each table involved in the query and use the Eloquent ORM to build the query. Here's an example:

Create models for the "warehouses", "warehouse_locations", "settings", and "stock_balances" tables:

// Warehouse model
class Warehouse extends Model
{
    protected $table = 'warehouses';
}

// WarehouseLocation model
class WarehouseLocation extends Model
{
    protected $table = 'warehouse_locations';
}

// Setting model
class Setting extends Model
{
    protected $table = 'settings';
}

// StockBalance model
class StockBalance extends Model
{
    protected $table = 'stock_balances';
}

Use the models to build the query:

$warehouses = Warehouse::select('wh.code', 'wh.name', 'loc.location_type_id', 's.name', DB::raw('count(distinct loc.id) as total_location'), DB::raw('(select count(distinct stk.location_id ) from stock_balances stk  where stk.warehouse_id = wh.id and stk.inventory_status ="20") as total_occupied'))
    ->join('warehouse_locations as loc', 'wh.id', '=', 'loc.warehouse_id')
    ->join('settings as s', 'loc.location_type_id', '=', 's.id')
    ->groupBy('wh.code', 'wh.name', 'wh.id', 'loc.location_type_id')
    ->get();

You can then loop through the results to display them:

@foreach ($warehouses as $warehouse)
    <p>{{ $warehouse->code }} - {{ $warehouse->name }} - {{ $warehouse->location_type_id }} - {{ $warehouse->name }} - {{ $warehouse->total_location }} - {{ $warehouse->total_occupied }}</p>
@endforeach

Snapey

3 years ago

Level 122

What have you tried?

What models and relationships have you created?

sharonnsp

3 years ago

Level 1

I need retrieve the data from stockBalance and Location, in the stock_balance table don have temperature title so i have to use ID to get the value so i've tries this method.

$Temperature_occupied = StockBalance::query() ->withCount('location') ->whereHas('location', fn ($query) => $query->where('warehouse_id', $activeFilter)) ->get() ->pluck('location_count','name') ->toArray();

`$Temperature_total = Location::query()
  ->withCount('id')
 ->whereHas('id', fn ($query) => $query->where('warehouse_id', $activeFilter))
 ->get()
->pluck('id_count', 'name')
->toArray();`

In the stock balance model created relationship with location and create casts for temperature and the temperature is enum class.

So now i don know how to retrieve the data, in this case. Im sorry for my bad explanation, i try my best to explain what's my problem. Hope you can give some idea thanks

Please or to participate in this conversation.