Laravel 7 previous and next records with alphabetical order

Question

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();
}

Answer 1

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=''");