connect two tables and get rows in laravel

Question

I have tables:

roles:

id name

role_user:

role_id user_id company_id user_type

I want company_id shows all his role names.

roles->id is role_user->role_id.

my code:

$role = DB::table('role_user')->select('role_id')->where('company_id', $cp)->get();
foreach ($role as $key) {
    $role = $key;
}

$roles = Role::where('id', $role->role_id)->get();

This code shows only one role name but must show 3. if I dd($role) before foreach then it gives array of 3 role_id

Answer 1

connect them using [join][1]

$roles = Role::select('roles.*')
->join('role_user', 'role_user.role_id','=','roles.id')
->where('company_id', $cp)
->get();

Answer 2

You can use table joins to achieve the desire output:

$role = DB::table('role_user')
->leftjoin('roles', 'role_user.role_id', '=', 'roles.id')
->where('company_id', $cp)
->get();