In my Laravel project, I have a Ticket model and a Player model.
The Ticket model has the columns: id, external_player_id, hit_quota, and created_at. It has a belongsTo relationship with the Player model.
The Player model has the columns: id, external_id, name, and status. It has a hasMany relationship with the Ticket model.
I have a TicketController with a method currentTopTickets. This method should:
Return the top 100 tickets based on hit_quota in descending order.
If multiple tickets have the same hit_quota, the older ticket (by created_at) should rank higher.
Only include tickets whose related Player has a status of 0 (active).
Ensure the result includes only the best ticket per player, even if a player has multiple tickets in the top 100. Does anyone can help me with query for this ?
@niiwill Have you tried anything so far? You may share, that will be easy to suggest.
However, here is a wild suggestion!
$topTickets = DB::table(DB::raw('(
SELECT t.*, ROW_NUMBER() OVER (
PARTITION BY t.external_player_id
ORDER BY t.hit_quota DESC, t.created_at ASC
) AS row_num
FROM tickets t
JOIN players p ON p.external_id = t.external_player_id
WHERE p.status = 0
) AS ranked_tickets'))
->where('row_num', 1) // Only include the top-ranked ticket per player
->orderBy('hit_quota', 'DESC')
->orderBy('created_at', 'ASC')
->limit(100)
->get();
