Laravel groupBy date-CodePudding

I want to sort the records in the transactions table by date and supplier as in the table design. The code I wrote in the controller did not work, what should I do?

Code in MonthlyTransactionsController

$transactions= Transactions::whereBetween('date', [$startDate, $endDate])
            ->select('product_id', 'supplier_id', 'total', 'date')
            ->latest('date')
            ->get();  

$transactionsByDate = $transactions
                ->groupBy('date')
                ->all();

blade file table code

<table id="monthlytransactions" >
<thead>
<tr>
<th>Supplier</th>
@foreach($dates as $date)
<th>{{ $date->format('d.m.Y') }}</th>
@endforeach
</tr>
</thead>
<tbody>
    
@foreach($suppliers as $supplier)
<tr>
<td>{{ $supplier->name}}</td>
@foreach($dates as $date)
// I want to show the total data here. Accordingly, how should my query be?
<td>{{ data_get($transactionsByDate,$date->total) }}</td>
@endforeach
</tr>
@endforeach
</tbody>
</table>

Database Table Design

DB transactions table

HTML Table Design

Html table design

dd($transactionsByDate) outline

1 => Illuminate\Database\Eloquent\Collection {#354 ▶}
    2 => Illuminate\Database\Eloquent\Collection {#346 ▶}
    3 => Illuminate\Database\Eloquent\Collection {#8205 ▶}
    4 => Illuminate\Database\Eloquent\Collection {#8204 ▶}
    5 => Illuminate\Database\Eloquent\Collection {#8203 ▶}
    9 => Illuminate\Database\Eloquent\Collection {#8202 ▶}
    10 => Illuminate\Database\Eloquent\Collection {#8201 ▶}
    11 => Illuminate\Database\Eloquent\Collection {#8200 ▶}
    13 => Illuminate\Database\Eloquent\Collection {#8199 ▶}
    14 => Illuminate\Database\Eloquent\Collection {#8198 ▶}
    15 => Illuminate\Database\Eloquent\Collection {#8197 ▶}
    16 => Illuminate\Database\Eloquent\Collection {#8196 ▶}
    20 => Illuminate\Database\Eloquent\Collection {#8195 ▼
      #items: array:40 [▼
        0 => App\Models\Transaction {#9453 …30}
        1 => App\Models\Transaction {#9454 …30}
        2 => App\Models\Transaction {#9455 …30}
        3 => App\Models\Transaction {#9456 …30}
        4 => App\Models\Transaction {#9457 …30}
        5 => App\Models\Transaction {#9458 …30}
        6 => App\Models\Transaction {#9459 …30}
        7 => App\Models\Transaction {#9460 …30}
        8 => App\Models\Transaction {#9461 …30}
        9 => App\Models\Transaction {#9462 …30}
        10 => App\Models\Transaction {#9463 …30}
        11 => App\Models\Transaction {#9464 …30}
        12 => App\Models\Transaction {#9465 …30}
        13 => App\Models\Transaction {#9466 …30}
        14 => App\Models\Transaction {#9467 …30}
        15 => App\Models\Transaction {#9468 …30}
        16 => App\Models\Transaction {#9469 …30}
        17 => App\Models\Transaction {#9470 …30}
        18 => App\Models\Transaction {#9471 …30}
        19 => App\Models\Transaction {#9472 …30}
        20 => App\Models\Transaction {#9473 …30}
        21 => App\Models\Transaction {#9474 …30}
        22 => App\Models\Transaction {#9475 …30}
        23 => App\Models\Transaction {#9476 …30}
        24 => App\Models\Transaction {#9477 …30}
        25 => App\Models\Transaction {#9478 …30}
        26 => App\Models\Transaction {#9479 …30}
        27 => App\Models\Transaction {#9480 …30}
        28 => App\Models\Transaction {#9481 …30}
        29 => App\Models\Transaction {#9482 …30}
        30 => App\Models\Transaction {#9483 …30}
        31 => App\Models\Transaction {#9484 …30}
        32 => App\Models\Transaction {#9485 …30}
        33 => App\Models\Transaction {#9486 …30}
        34 => App\Models\Transaction {#9487 …30}
        35 => App\Models\Transaction {#9488 …30}
        36 => App\Models\Transaction {#9489 …30}
        37 => App\Models\Transaction {#9490 …30}
        38 => App\Models\Transaction {#9491 …30}
        39 => App\Models\Transaction {#9492 …30}
      ]
      #escapeWhenCastingToString: false

CodePudding user response：

I try this way but the total value comes as null

Code in MonthlyTransactionsController

$transactions= Transactions::whereBetween('date', [$startDate, $endDate])
            ->select('product_id', 'supplier_id', 'total', 'date')
            ->latest('date')
            ->get();  

$transactionByDate= collect($transactions)
->groupBy('date')
->map(function($items) {
    return $items
        ->groupBy('supplier_id')
        ->map(fn($items) => $items->get('total'));
})
->toArray();
dd($transactionByDate);

Output:

array:11 [▼
  "2022-02-09" => array:74 [▼
    127 => null
    65 => null
    43 => null
    .
    .
    .   
  ]

CodePudding user response：

Try using facades:

use Illuminate\Support\Facades\DB;

$transactions = DB::table('transactions')
                      ->select('product_id', 'supplier_id', 'total', 'date')
                      ->where([
                               ['date', '>=', $startDate],
                               ['date', '<=', $endDate]
                              ])
                      ->groupBy('date')
                      ->get();