How to show the total on the last rows with same value? (Laravel)

Question

I have table 'transaction_items' in my dtaabse that looks similar to this...

id	transaction_id	qty	unit_price
1	100	1	10.00
1	100	2	10.00
1	100	3	10.00
1	200	1	20.00
1	200	1	20.00
1	300	1	15.00

Current I fetch the data: $data['transaction_items'] = DB::table('transaction_items')->get();

and run a foreach loop:

foreach ($transaction_items as $trans) {
 <tr>
  <td> {{ $trans->transaction_id}} </td>
  <td> {{ $trans->qty}} </td>
  <td> {{ $trans->unit_price}} </td>
  <td> * WHOLE TRANSACTION TOTAL</td>

This outputs the data perfectly as you would expect. Though my last value (*WHOLE TRANSACTION TOTAL) of my output is running another a query and foreach loop to get the 'total' of the whole transaction.

<tr>
 <td>
  <?php 
  $totals = DB::table('transaction_items')->select('qty', 'unit_price')->where('transaction_id', '=', $trans->transaction_id)->get();
  $ftot = 0;
  foreach ($totals as $total) {
    $ftot += $total->qty * $total->unit_price;
  }
  echo '&pound;'.number_format($ftot, 2);
  ?>
 </td>
</tr>

I know this is probably the wrong way of doing it, but that's why I am.

My current output looks like this...

transaction_id	qty	unit_price	Trans Total
100	1	10.00	60.00
100	2	10.00	60.00
100	3	10.00	60.00
200	1	20.00	40.00
200	1	20.00	40.00
300	1	15.00	15.00

I would like it to look like this, where it shows the total after the last row with the the same transaction_id.

transaction_id	qty	unit_price	Trans Total
100	1	10.00
100	2	10.00
100	3	10.00	60.00
200	1	20.00
200	1	20.00	40.00
300	1	15.00	15.00

Hope this makes sense and thanks in advance.

EDITED EXTRA

I have tried using the 'reduce' method but getting...

$totals = DB::table('transaction_items')
    ->select('qty', 'unit_price')
    ->where('transaction_id', '=', $trans->transaction_id)
    ->get()
    ->groupBy('transaction_id')->each(function($item) {
    $sum = $item->reduce(fn($carry, $product) => $carry + ($product['qty'] * $product['unit_price']));
    $lastRow = $item->pop();
    $lastRow['sum'] = $sum;
    $item->push($lastRow);
    return $item;
})->flatten(1);

$ftot = 0;     
foreach ($totals as $total) {
 $ftot += $total->qty * $total->unit_price;
} 
echo '&pound;'.number_format($ftot, 2);

But I get this error: Cannot use object of type stdClass as array

If I do 'print_r' on my original $totals, this is an example of one of the rows..

Illuminate\Support\Collection Object ( 
 [items:protected] => Array ( 
  [0] => stdClass Object ( 
   [transaction_id] => 20940 
   [qty] => 4.00 
   [unit_price] => 80.0000 
  ) 
  [1] => stdClass Object ( 
   [transaction_id] => 20940 
   [qty] => 6.00 
   [unit_price] => 10.0000 
  ) 
  [2] => stdClass Object ( 
   [transaction_id] => 20940 
   [qty] => 308.00 
   [unit_price] => 0.4000 
  ) 
  [3] => stdClass Object ( 
   [transaction_id] => 20940 
   [qty] => 308.00 
   [unit_price] => 0.0500 
  ) 
  [4] => stdClass Object ( 
   [transaction_id] => 20940 
   [qty] => 1.00 
   [unit_price] => 10.3000 
  ) 
 ) 
)

Maksim Maksim · Accepted Answer · 2021-07-31T14:46:14

Simple - Laravel collections is powerful

$total = $total->reduce(function ($carry, $item) {
    return $carry + ($item->qty * $item->unit_price);
});

Or inline (I did not checked this code, probably not working)

$total = $total->reduce(fn ($carry, $item) => $carry + ($item->qty * $item->unit_price));

UPDATED

I did not read question properly, sorry. But collections still powerful. Btw, I think, you must think about this behaviour - more nice way is add one dimension - group by transaction and add subtotal for each group

$totals = DB::table('transaction_items')
    ->select('qty', 'unit_price')
    ->where('transaction_id', '=', $trans->transaction_id)
    ->get()
    ->groupBy('transaction_id')->each(function($item) {
        $sum = $item->reduce(fn($carry, $product) => $carry + ($product['qty'] * $product['unit_price']));
        $lastRow = $item->pop();
        $lastRow['sum'] = $sum;
        $item->push($lastRow);
        return $item;
})->flatten(1);

How to show the total on the last rows with same value? (Laravel)

1 Answers