[FIXED] What is the best practice to query this type of data in laravel?

Issue

I have 3 tables structure like below:-

products

id	name
1	apple
2	orange

branches

id	name
1	Branch 1
2	Branch 2

stocks

id	branch_id	product_id	qty
1	1	1	10
2	1	2	20
3	2	1	30
4	2	2	40

Expected result:-

No.	Name	Branch 1	Branch 2	Total
1	apple	10	30	40
2	orange	20	40	60

Below is my code it is working and can get the expected result but I don't like the way I do especially the part $stock[0]->product->name, I think there must be another proper way to do this better not to loop the branches in blade, is it possible to get everything done in query?

StockController

$branches = Branch::get();
$stocks = Stock::get()->groupBy('product_id');

//$stocks->toArray() output
array:2 [
  1 => array:2 [
     0 => array:4 [
        "id" => 1
        "product_id" => 1
        "branch_id" => 1
        "qty" => 10
     ]
     1 => array:4 [
        "id" => 1
        "product_id" => 1
        "branch_id" => 2
        "qty" => 30
     ]
  ]
  2 => array:2 [
     0 => array:4 [
        "id" => 1
        "product_id" => 2
        "branch_id" => 1
        "qty" => 20
     ]
     1 => array:4 [
        "id" => 1
        "product_id" => 2
        "branch_id" => 2
        "qty" => 40
     ]
  ]
]

stock_index.blade.php

@foreach ($stocks as $stock)
            <tr>
                <td>{{ $loop->iteration }}</td>
                <td>{{ $stock[0]->product->name }}</td>
                @foreach ($branches as $branch)
                    <td>{{ $stock->firstWhere('branch_id', $branch->id)->qty ?? 0 }}</td>
                @endforeach
                <td>{{ $stock->sum('qty') }}</td>
            </tr>
@endforeach

Solution

Hope this helps

$branches = Branch::pluck('name', 'id');

$query_string = "";

foreach ($branches as $key => $value) {
    $query_string .= "sum(CASE WHEN stocks.branch_id = '". $key ."' THEN stocks.qty END) as " . str_replace(' ', '_', $value) . "_sum , ";
}

$query_string .= 'sum(stocks.qty) as total';

$stocks = Stock::join('products', 'stocks.product_id', '=', 'products.id')->select(
    'products.name as product_name',
    DB::raw($query_string)
)->groupBy('product_id')->orderBy('product_id','asc')->get(); 

Answered By - Manuel Barretto

Answer Checked By - Mary Flores (PHPFixing Volunteer)