1

In my edit form, I want to add a button to save and edit the following user.

I am trying that the "next" button takes me to the next record in alphabetical order.

I have only managed to order by ID

// In a model
public function next(){
  return User::where('id', '>', $this->id)->orderBy('nombre','asc')->first();
}
public  function previous(){
  return User::where('id', '<', $this->id)->orderBy('nombre','desc')->first();
}
Alexander Cuestas
  • 13
  • 2
  • 3
    Sort all and cache them in the frontend. DB server hits and sorting the entire dataset again just to get the next or previous one is too costly – nice_dev Oct 05 '21 at 05:17
  • Does changing your `where` clause to check for name > the name of the current record your editing work? – Nigel Ren Oct 05 '21 at 05:44

1 Answers1

0

You need to use the rank function of mysql and raw query

For previous record

\DB::select('SELECT * FROM (SELECT RANK() OVER (ORDER BY nombre ASC) ranking, users.* FROM `users`) user where ranking < {previous_record_rank} ORDER BY ranking asc limit 1;');

For next record

\DB::select('SELECT * FROM (SELECT RANK() OVER (ORDER BY nombre ASC) ranking, users.* FROM `users`) user where ranking > {previous_record_rank} ORDER BY ranking asc limit 1;');

And you need to set the mysql.strict to false in config/database.php

Or

\DB::statement("SET SQL_MODE=''");
parth
  • 1,803
  • 2
  • 19
  • 27