So I have a list of users, who receive votes, they are ordered by votes DESC, but then I have another query that gets their current position I.E. 1st place, 2nd place, 10th, place, etc.
The problem is when people have the same number of votes, the position isn't in sync with the order they appear in,
For example, a user with 1 vote at position 130 will appear before another user with 1 vote at position 125.
It is not a huge problem but a really annoying one, is there an efficient way of handling this? I thought about calculating position and storing it as a column but the problem is I would have to recalculate position for everyone after every vote, which is too inefficient.
Here are the two queries:
// get the list of participants ordered by votes DESC Participant::with('user') ->where('contest_id', $cid)->orderBy('vote_count', 'DESC') ->paginate(28); // get the position of a participant \DB::select( 'SELECT position FROM (SELECT user_id, @rownum := @rownum + 1 as position FROM contest_participants JOIN (SELECT @rownum := 0) r WHERE contest_id = ? ORDER BY vote_count DESC) b WHERE user_id = ?', [$cid, $pid]);