How to get all the user data from another table and include them on user list in laravel?

Question

I am using 2 tables upon displaying my users in my users>index.blade

these 2

USER table

and COLLECTOR_MEMBERS table

The result is this

Now my problem is I want to connect to the other table called

COMMISSIONS table

to achieve this result

MODELS

COMMISSIONS Model

USER

COLLECTOR MEMBER

USER Controller index function

public function index(Request $request)
{
    $users = User::all();           
    $admins = User::whereHas('roles', function ($q) {
      $q->where('roles.name', '=', 'admin');
    })->get();

    $collectorList = User::whereHas('roles', function ($q) {
      $q->where('roles.name', '=', 'collector');
    })->with('collectorList')->get();

    $borrowers = User::whereHas('roles', function ($q) {
      $q->where('roles.name', '=', 'borrower');
    })->get();

    $userProfile = Auth::user()->id;

    if (Auth::User()->hasRole(['borrower','collector'])){
        return redirect('dashboard/profile/'.$userProfile);
    }

    return view('dashboard.users.index', compact('users','profile'))
            ->with('admins',$admins)
            ->with('borrowers',$borrowers)
            ->with('collectorList',$collectorList);
            // ->with('collectorBorrowers',$collectorBorrowers);
}

How wan I display the commission_amount column from commissions table? to make my list like this

Answer 1

You could use sum aggregate function, your code should look like this.

$collectorList = User::whereHas('roles', function ($q) {
      $q->where('roles.name', '=', 'collector');
    })->with(['collectorCommission' => function($query) {
          $query->sum('commission_amount');
}])->get();

Assuming that you have this relationship in your user model

public function collectorCommission() [
   return $this->hasMany('App\Commissions', 'user_id');
}

You cant use belongsToMany relationship since this relationship requires you an intermediary table in your second argument.

You should use hasMany relationship considering that one user has many commissions.