How to display order by null last in laravel

These code snippets will help to display records in order by null values at last in Laravel. Unfortunately, orderBy() will always put null values first if you are sorting by ascending (ASC) order. But it works fine with descending ( DESC) order in case if you want to put null values at last.

Answers 2
  • Order by null values at last using ascending order in laravel 8

    $projects = \App\Models\Project::orderByRaw('ISNULL(order_by), order_by ASC')->get();
    //Or 
    $projects = \App\Models\Project::orderBy(DB::raw('ISNULL(order_by), order_by'), 'ASC')->get();
    
    0

    You can display records in ascending order with null values at last. To achieve this you should have an order_by column in your project table with an integer type that allows null values.

  • Order by null values at last on pivot table with orderByRaw expression using ascending (ASC) order in laravel

    //Method 1
    //You can use orderByRaw expression in the model method to sort the records in ascending order with null values at last. </p>
    public function projects(){
        return $this->belongsToMany(Project::class)
                    ->orderByRaw('ISNULL(order_by), order_by ASC')
                    ->withPivot('order_by')
                    ->withTimestamps();
    }
    
    //Method 2
    //You can use the orderByRaw expression in the controller's method to sort records in ascending order with null values at last.
    //app\Models\<User>.php
    // Projects method with belongsToMany relation in user model
    public function projects(){
           return $this->belongsToMany(Project::class)
                          ->withPivot('order_by')
                          ->withTimestamps();
    }
    
    //app\Http\Controllers\<YourController>.php
    //Controller’s method
    public function getUserWithProjects(){
         $users = User::with(['projects' => function($q){
                $q->orderByRaw('ISNULL(order_by), order_by ASC');
         }])->first();
        return view('user.index', compact('users'));
    }
    
    //resources\views\<user>\<index>.blade.php
    @foreach($users->projects as $project)
          {{ $project->name }}  {{ $project->pivot->order_by }} <hr>
    @endforeach
    
    0

    There is a pivot table with an additional column name order_by. The order_by column is an integer column that allows null values. We have to display records in order with the value of 1 in the order_by column should be first, value of 2 should be second and so on. 

    We can use orderByRaw expression in laravel to display records in ascending order with null values at last.

  • Back to code snippet queries related laravel