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

user_7073918 0
Viewed 123

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
laravel group-by
2 Answers

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();
user_186593620

I suggest you use this code: ‌

$query = "";

foreach (Branch::query()->pluck('name', 'id') as $key => $value) {
    $query .= "sum(CASE WHEN stocks.branch_id = '". $key ."' THEN stocks.qty END) as " . str_replace(' ', '_', $value) . "_sum , ";
}

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

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