ran.sql

SELECT participants.*, 
RANK() over ( ORDER BY total_time ) rank, 
(SELECT SUM(time) FROM `leaderboards` WHERE `participant_id` = `event_participant`.`participant_id` and `event_id` = `event_participant`.`event_id` LIMIT 1) as `total_time`, 
`event_participant`.`event_id` as `pivot_event_id`, `event_participant`.`participant_id` as `pivot_participant_id` 
FROM `participants` 
inner join `event_participant` on `participants`.`id` = `event_participant`.`participant_id` 
WHERE `event_participant`.`event_id` in (29) ORDER BY `total_time` ASC


$participants = Participant::select('participants.*',
    DB::raw('RANK() OVER (ORDER BY total_time) rank'),
    DB::raw('(SELECT SUM(time) FROM leaderboards WHERE participant_id = event_participant.participant_id and event_id = event_participant.event_id LIMIT 1) as total_time'),
    'event_participant.event_id as pivot_event_id',
    'event_participant.participant_id as pivot_participant_id'
)
->join('event_participant', 'participants.id', '=', 'event_participant.participant_id')
->whereIn('event_participant.event_id', [29])
->orderBy('total_time', 'asc')
->get();