join a single table twice in join query

I have 3 tables users and meeting table and user_role. In user table I have user_name and user_role fields and in meeting table I have the column meeting_id assigned_username and created_by_user_name fields and In user_roles table have id and role .

 users 
  user_name  role   
   a          1
   b          2
   c          3

meeting table 
    meeting_id assigned_username  created_by_user_name
     m1          a                 b
     m2          b                 -
     m3          b                 a

 user_roles
   id  user_role
   1    role1
   2    role2
   3    role3

                           

I want to get the assigned_user_name’s user_role and created_by_user_name’s user_role in single query. I have tried as

  $user=DB::table('meeting')
        ->leftjoin('users','users.user_name','=','meeting_after_validation.assigned_user_name')
        ->leftjoin('users as ca','ca.user_name'),'=','meeting_after_validation.created_by_name')
        ->leftjoin('user_roles','users.user_role','=','user_roles.id')
        ->leftjoin('user_roles as carole','ca.user_role','=','carole.id')
        ->whereIn('carole.is',['1','2'])
        ->select('users.user_name','user_roles.role','carole.role')->toArray();

but getting

Call to a member function whereIn() on array

how to get role of assigned_user_name and created_by_user_name.

Answer

You must use get() before you can use toArray() if you want to get the values as an array.

Also, there may be a typo for your whereIn, should it be carole.id?

Here is what the query could look like in SQL:

SELECT
    
    meeting.meeting_id,
    
    users_assigned.user_name as assigned_user,
    role_assigned.user_role as assigned_user_role,
    
    users_created.user_name as created_user,
    role_created.user_role as created_user_role
    
FROM meeting
    
LEFT JOIN users as users_assigned ON meeting.assigned_username = users_assigned.user_name
LEFT JOIN user_roles as role_assigned ON users_assigned.role = role_assigned.id

LEFT JOIN users as users_created ON meeting.created_by_user_name = users_created.user_name
LEFT JOIN user_roles as role_created ON users_created.role = user_roles.id

WHERE role_assigned.id IN (1, 2)

And in Laravel query builder:

$user = DB::table('meeting')
    ->select([
        'meeting.meeting_id',
        'users_assigned.user_name as assigned_user',
        'role_assigned.user_role as assigned_user_role',
        'users_created.user_name as created_user',
        'role_created.user_role as created_user_role',
    ])
    ->leftJoin('users as users_assigned', 'meeting.assigned_username', '=', 'users_assigned.user_name')
    ->leftJoin('user_roles as role_assigned', 'users_assigned.role', '=', 'role_assigned.id')
    ->leftJoin('users as users_created', 'meeting.created_by_user_name', '=', 'users_created.user_name')
    ->leftJoin('user_roles as role_created', 'users_created.role', '=', 'user_roles.id')
    ->whereIn('role_assigned.id', [1, 2])
    ->get()
    ->toArray();