Querying records that are referenced in another table from model in Eloquent-CodePudding

I have two models, Prize and Ticket. Prize has many Tickets associated to it, There is also a winner_ticket_id which is a one-to-one relationship.

Now what I'm trying to do is query all my Tickets, but only the ones that are referenced as the ticket_winner_id in Prize.

I am using the following code:

$tickets = Ticket::with('user')->whereHas('prize', function ($query) {
    $query->where('ticket_winner_id');
})->orderBy('created_at', 'DESC')->paginate(30);

This code above however brings back all my tickets, instead of the winners. Can someone show how I can query only the winning tickets?

I know it would be easy to just start from Prize and make a relationship to the winner column, but I want to start from the Ticket collection so I can inject this into some reusable blade templates.

CodePudding user response：

It's better to write it that way:

Ticket::with('user')
    ->innerJoin('prizes', 'prizes.ticket_winner_id', 'tickets.id')
    ->orderBy('tickets.created_at', 'DESC')
    ->paginate(30);

And it will be much better if you added a flag in the tickets table to tell that ticket is the winner or you can add the prize_id in tickets table.

Hope this can solve.