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?
CodePudding user response:
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');