How to do Laravel Eloquent get with a specific item appearing first for pagination

I have a use case where I need to get all the rows from a table with the currently selected row appearing first and show the rest as the user scrolls through the table. I currently have it setup where I use the eloquent find to fetch the individual item and then use the eloquent get and merge the results if the page number is 1. Is there a better way to handle this or is this the best way to handle this use case?

rodrigo.pedra

7 years ago

Level 56

There are some other ways to do that using a single eloquent query.

#1 is to fetch all records and then custom sort in the collection

$router->get( 'test', function () {
    $current = 2; // current id

    $collection = \App\MyModel::all(); // only one db call

    $sorted = $collection
        ->sortBy('name') // 1. sort by name
        ->values() // reindex the collection
        ->sortBy(function ($item, $index) use ($current) {
            if ($item->id == $current) return -1; // 3. bumps selected to top 

            return $index;
        });

    return $sorted;
} );

#2 use an custom order by directly in the DB query:

$router->get( 'test', function () {
    $current = 2; // current id

   // 1. use a raw statement to bump the current to top
    $collection = \App\MyModel::orderByRaw("CASE WHEN id = {$current} THEN 0 ELSE 1 END") 
        ->orderBy('name') // 2. order by name
        ->get();

    return $collection;
} );

Please or to participate in this conversation.