whereIn function within a sub query within eloquent doesnt filter any records

I have a Prize, Ticket and User model. A prize can have many tickets, and a ticket can only be associated to one User.

Each Prize will have one Winning Ticket, what I am trying to do is list all my Users that have a winning Ticket like so:

$winning_tickets = Prize::WinnerSelected()->get('ticket_winner_id')->pluck('ticket_winner_id');

$users = User::with(['tickets' => function($query) use ($winning_tickets) {
     $query->whereIn('id', $winning_tickets);
}])->get();

$winning_tickets returns an array of winning ticket ids, but the $users collection returns ALL my users, even users that have no ticket records.

Can anyone explain what I am doing wrong?

Answer

with() doesn’t actually filter the User Collection being returned. To do that, you need to use whereHas():

$winningTickets = Prize::WinnerSelected()->get('ticket_winner_id')->pluck('ticket_winner_id');

$users = User::whereHas('tickets', function($query) use ($winningTickets) {
     $query->whereIn('id', $winningTickets);
})->get();

Now, the $users Collection will only contain User records that have one or more Ticket records matching the given ticket_winner_id in $winning_tickets.

If you need to, you can use both with() and whereHas() to filter and eager load the associated Ticket records:

$winningTickets = Prize::WinnerSelected()->get('ticket_winner_id')->pluck('ticket_winner_id');

$filterClause = function ($query) use ($winningTickets) {
  return $query->whereIn('id', $winningTickets);
};

$users = User::with(['tickets' => $filterClause])
->whereHas('tickets', $filterClause)
->get();

Define the the function ($query) as a reusable clause to avoid repetition, and voila!

Sidenote, you don’t need to chain ->get() into ->pluck(); both Builder and Collection classes have a ->pluck() method, so this is valid:

$winningTickets = Prize::WinnerSelected()->pluck('ticket_winner_id');