Using Laravel’s WhereIn to search multiple tables

I have 3 SQL tables.

1. clients
2. events
3. client_events

Because a client can have multiple events, I made the third table to show those relationships. I am using the following code to retrieve all of the clients that have the have a record matching this event, but it is only returning 1 record when there are multiple.

$eventHosts = DB::table('clients')->whereIn('id', function($query) {
    $query->select('client_id')->from('client_events')->where('event_id', '=', explode('/', $_SERVER['REQUEST_URI'])[2]);
})->get();

What am I overlooking?

Answer

You can fetch the ids first, then pass to the whereIn query.

$clientIds = DB::table('client_events')
  ->where('event_id', explode('/', $_SERVER['REQUEST_URI'])[2])
  ->pluck('client_id')
  ->toArray();

$eventHosts = DB::table('clients')->whereIn('id', $clientIds)->get();