Oct 6, 2022

Level 1

pass multiple variable in one foreach loop in laravel blade

How can I pass multiple variables in one foreach loop? i have a table that want to fetch data and i store the data in multiple variables in my controller

controller

public function index() {
        $transactions = Transaction::select(array('date', 'transaction_type', 'quantity', DB::raw('SUM(amount) AS amount'), DB::raw('SUM(quantity) AS count')))
        ->groupBy('date')
        ->orderBy('date', 'DESC')
        ->get();
        $container = Transaction::where('transaction_type', 'deliver')->select(array(DB::raw('SUM(quantity) AS deliver')))->get();
        $container1 = Transaction::where('transaction_type', 'pick-up')->select(array(DB::raw('SUM(quantity) AS pickup')))->get();

        return view('dashboard',  compact('transactions', 'container', 'container1'));
    }

blade

<table class="table-striped" id="dailysales">
    <thead>
      <tr class="table-info">
        <th> DATE </th>
        <th> <center>DELIVER</center> </th>
        <th> <center>PICK UP</center> </th>
        <th> <center>TOTAL CONTAINER</center> </th>
        <th> <center>TOTAL AMOUNT</center> </th>
    
      </tr>
    </thead>
    <tbody>
      @foreach ($transactions as $transaction)
      <tr>
        <td>{{ $transaction->date }}</td>
        <td><center>{{$container->deliver }}</center></td>
        <td><center>{{$container1->pickup }}</center></td>
        <td><center>{{$transaction->count}}</center></td>
        <td><center>₱{{$transaction->amount}}</center></td>
      </tr>
      @endforeach
      
    </tbody>
  </table>

Here is the error I got Property [deliver] does not exist on this collection instance.

rodrigo.pedra

3 years ago

Level 56

Try using ->first() instead of ->get()

 $container = Transaction::where('transaction_type', 'deliver')->select(array(DB::raw('SUM(quantity) AS deliver')))->first();
        $container1 = Transaction::where('transaction_type', 'pick-up')->select(array(DB::raw('SUM(quantity) AS pickup')))->first();

If you need the deliver and pickup sums for different transactions, you are lacking a group by clause, maybe by date, as you are grouping in the first clause.

1 like

rodrigo.pedra

3 years ago

Best Answer

Level 56

If the other sums needs to be grouped by and constrained as the transactions, you can use a CASE SQL clause to filter down the sums:

public function index()
{
    $transactions = Transaction::query()
        ->select(['date', 'transaction_type', 'quantity'])
        ->selectRaw('SUM(amount) AS amount')
        ->selectRaw('SUM(quantity) AS count')
        ->selectRaw('SUM(CASE WHEN transaction_type = ? THEN quantity ELSE 0 END) AS deliver', ['deliver'])
        ->selectRaw('SUM(CASE WHEN transaction_type = ? THEN quantity ELSE 0 END) AS pickup', ['pickup'])
        ->groupBy('date')
        ->orderByDesc('date')
        ->get();

    return view('dashboard', ['transactions' => $transactions]);
}

Then in your blade file:

@foreach ($transactions as $transaction)
    <tr>
        <td>{{ $transaction->date }}</td>
        <td style="text-align: center">{{ $transaction->deliver }}</td>
        <td style="text-align: center">{{ $transaction->pickup }}</td>
        <td style="text-align: center">{{ $transaction->count }}</td>
        <td style="text-align: center">{{ $transaction->amount }}</td>
    </tr>
@endforeach

1 like

plue

3 years ago

Level 1

@rodrigo.pedra wow this actually works! Can you please explain this line Im still learning and want to know how this works

->selectRaw('SUM(CASE WHEN transaction_type = ? THEN quantity ELSE 0 END) AS deliver', ['deliver'])
 ->selectRaw('SUM(CASE WHEN transaction_type = ? THEN quantity ELSE 0 END) AS pickup', ['pickup'])

rodrigo.pedra

3 years ago

Level 56

@plue well, this case condition is a SQL statement, similar to a switch or the newer match clause in PHP.

So when we tell MySQL to:

SUM(CASE
    WHEN transaction_type = 'deliver' THEN quantity 
    ELSE 0 
END) AS deliver

It will check every record and test for the first WHEN clause.

If for that record the condition (transaction_type = 'deliver') is met, then the CASE will return that record's quantity value.

If no WHEN clauses match (you can have more than one WHEN clauses), it will default to the ELSE clause, which in our case returns zero.

Well, if for the filtered records I sum quantity and for the rejected records I sum zero, than at the end I will have only summed the quantity column from the records I want.

Same for the other SUM.

Hope this makes it clearer =)

1 like

rodrigo.pedra

3 years ago

Level 56

For a single condition test you can also use the IF() function:

So those two columns could be written as:

->selectRaw('SUM(IF(transaction_type = ?, quantity, 0)) AS deliver', ['deliver'])
->selectRaw('SUM(IF(transaction_type = ?, quantity, 0)) AS pickup', ['pick-up'])

Which is shorter.

The IF() SQL function accepts three parameters, the first will be evaluated as a boolean, if the first is evaluated as true, then the function returns the value of the second parameter, otherwise it returns the value of the third parameter.

I usually use the CASE for personal preference (not all older databases supported IF() and old habits die hard), but choose what looks better to you.

reference:

https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html#function_if

1 like

plue

3 years ago

Level 1

@rodrigo.pedra This helps a lot thank you so much!!!

1 like

rodrigo.pedra

3 years ago

Level 56

@plue you're welcome! Have a nice day =)

1 like

Please or to participate in this conversation.