How to sort data and get 10 items BEFORE and 10 items AFTER in Laravel?

Question

Laravel

Hello! I’m doing the rating of players in the game on , I can’t figure out how to do the following in the minimum number of requests:
Get the player’s place by id in the overall rating and get ONLY 10 players who have more points than the current player and 10 players who have less points, total get 21 items from the database, where the current player will be in the center. I am using the Laravel Query Builder.
Rating table structure:

[id] [user_id] [score]
1 1 0
2 2 50
3 3 0
4 4 280
5 5 1005
6 6 2000 
и т.д.

I want to get:

[id] [user_id] [score]
<тут ещё  8 игроков>
38 38 2545
6 6 2000
5 5 1005 - это нужный игрок (в центре выборки)
4 4 280
2 2 50
<тут ещё  8 игроков>

Answer 1

Try it like this... except for the score condition... as you write in the comments, you can add the date to orderBy... for cases with the same...

<?php

$rsUser = Model::where('user_id', ID_ЮЗЕРА)->first();

if($rsUser) {

    DB::statement(DB::raw('set @row := 0'));

    $rsFirst2Before = Model::where('score', '<=', $rsUser->score)->orderBy('score', 'DESC')->select(['*', DB::raw('@row := @row + 1 as `row`')])->limit(11);
    $rsAfter = Model::where('score', '>=', $rsUser->score)->where('user_id', '<>', $rsUser->user_id)->orderBy('score', 'ASC')->select(['*', DB::raw('@row : = @row + 1 as `row`')])->limit(10);
    
    $rs = $rsFirst2Before->union($rsAfter)->get();

    // dump($rs);
    
}

?>

PS: My answer also implies the serial number in the rating among all...
If it will work at all :) I haven't tested it, but it should))
Check)) The
serial number by the row key will be available...
UPD:
Tweaked it a little more answer... added where('user_id', '<>', $rsUser->user_id) so that there is no "in the next 10" of the same user, if there is one with the same rating...
UPD2:
Although there is a nuance here... if there are, for example, 30 users with the same rating...
In short, if it works, we'll figure it out further to bring it to the ideal :)
UPD3:
Also, consider the option with a temporary table ... at some time (or by event) to immediately record users and their places ...
For me, this is the best option ...

Answer 2

something like

SELECT score INTO @score FROM players WHERE id = :p_id;
SELECT * FROM (SELECT * FROM players p WHERE points > @score ORDER BY score LIMIT 10) ORDER BY score DESC
UNION
SELECT * FROM players WHERE points <= @score ORDER BY score DESC LIMIT 11;