php - Passing a variable from one query to another in same method in Laravel

Question

I am trying to pass a variable from one query to another (bellow is the code) If I use $data[0]->name this will give a fixed values which I need something variable like in $data[$i]->name or something.

Controller:

  public function accounts($country) {

    
    $data= DB::table("invoices")
        ->where("country", $country)
        ->select([ "name",
                    DB::raw("sum(case when type='income' then amount else 0 end) as income"),
                    DB::raw("sum(case when type='outcome' then amount else 0 end) as outcome")
                ])
        ->groupBy("name")
        ->orderBy("name", "DESC")
        ->get();


    $payments= DB::table("payments")
        ->where("name", $data[0]->name) // here is the variable I want to pass from the above $data query
        ->where("country", $country)
        ->select([  
                    DB::raw("sum(case when type='income' then amount else 0 end) as payment_income"),
                    DB::raw("sum(case when type='outcome' then amount else 0 end) as payment_outcome")
                ])
        ->groupBy("name")
        ->orderBy("name", "DESC")
        ->get();

    return view('accounting.accounts')
    ->with('accounts',$data)
    ->with('payments',$payments);

   }

View:

 @foreach($accounts as $account)
   @foreach($payments as $payment)
     <tr>
        <th>{{$account->name}}</th>
        <td>{{$account->income}}</td>
        <td>{{$payment->payment_income}}</td>
        <td></td>
        <td>{{$account->outcome}}</td>
        <td>{{$payment->payment_outcome}}</td>
        <td></td>
     <tr>
  @endforeach
@endforeach

Thank you in advance

question from:https://stackoverflow.com/questions/65903892/passing-a-variable-from-one-query-to-another-in-same-method-in-laravel

Answer 1

Here is what you can do

$names = $data->pluck('name');

$payments= DB::table("payments")
        ->where("country", $country)
        ->whereIn("name", $names) 
        ->select([  
                    DB::raw("sum(case when type='income' then amount else 0 end) as payment_income"),
                    DB::raw("sum(case when type='outcome' then amount else 0 end) as payment_outcome")
                ])
        ->groupBy("name")
        ->orderBy("name", "DESC")
        ->get();
return view('accounting.accounts')
->with('accounts',$data)
->with('payments',$payments);