How to Sort Null Values Last Using Eloquent in Laravel

How to sort NULL values last using Eloquent in Laravel

Laravel does not take into consideration the ISNULL method however, you can pass it in as a raw query and still make use of it as it's more efficient than IF statements and the results will stay the same if you ever go beyond 1000000 employees (accepted answer), like so:

public function employees()
{
    return $this->hasMany('Employee')
                ->orderBy(DB::raw('ISNULL(sortOrder), sortOrder'), 'ASC');
}

Update:
You can also use the orderByRaw() method:

public function employees()
{
    return $this->hasMany('Employee')
                ->orderByRaw('ISNULL(sortOrder), sortOrder ASC');
}

order by nulls first in descending order in laravel

In an ascending sort, null values appear last by default (and first in a descending sort). Postgres provide a way to override the default sort ordering null with option nulls first and nulls last.

You could use it with orderByRaw:

Source::orderByRaw('last_rank_update nulls first')

How to order a table based on null values?

What about something like this:

1. Get all from the table.
2. Using the collected results get all without null levels then sort them.
3. Using the collected results get all with null levels then sort them.
4. Merge the 2 sorted collection results and pluck the name.

// 1. Get all from the table.
$warehouses = Wharehouse::all();

// 2. Using the collected results get all without null levels then sort them.
$warehousesWithLevels = $warehouses->where('level', '!=', null)
                                        ->sortBy('level');

// 3. Using the collected results get all with null levels then sort them.
$warehousesWithoutLevels  = $warehouses->where('level', null)
                                        ->sortByDesc('id');

// 4. Merge the 2 sorted collection results and pluck the name.
$warehousesSorted = $warehousesWithLevels->merge($warehousesWithoutLevels)->pluck('name');

dd($warehousesSorted);

Or with the Scope I created in the model you can use:

Wharehouse::allSortedMyWay();

The above has one DB query then works with the collected results.

You can modify the sorting on whatever key best suits your need.

Tested as working with the following results:

Collection {#268 ▼
  #items: array:5 [▼
    0 => "hiroshima"
    1 => "osaka"
    2 => "nagoya"
    3 => "sapporo"
    4 => "tokyo"
  ]
}

Model I used:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Wharehouse extends Model
{
    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'warehouses';

    /**
     * Indicates if the model should be timestamped.
     *
     * @var bool
     */
    public $timestamps = false;

    /**
     * The attributes that are not mass assignable.
     *
     * @var array
     */
    protected $guarded = [
        'id',
    ];

    /**
     * Fillable fields for a Profile.
     *
     * @var array
     */
    protected $fillable = [
        'name',
        'level',
    ];
}

    /**
     * Return all warehousts sorted my way - Quick but not a true query scope.
     *
     * @return collection
     */
    public function scopeAllSortedMyWay()
    {
        $warehouses = Wharehouse::all();

        $warehousesWithLevels = $warehouses->where('level', '!=', null)
                                                ->sortBy('level');

        $warehousesWithoutLevels  = $warehouses->where('level', null)
                                                ->sortByDesc('id');

        return $warehousesWithLevels->merge($warehousesWithoutLevels)->pluck('name');
    }

Seeder I used:

<?php

use App\Wharehouse;
use Illuminate\Database\Seeder;

class WharehouseTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $items = [
            [
                'name'   => 'osaka',
                'level'   => 3,
            ],
            [
                'name'   => 'tokyo',
                'level'   => null,
            ],
            [
                'name'   => 'sapporo',
                'level'   => null,
            ],
            [
                'name'   => 'nagoya',
                'level'   => 4,
            ],
            [
                'name'   => 'hiroshima',
                'level'   => 1,
            ],
        ];

        foreach ($items as $item) {
            $newItem = Wharehouse::where('name', '=', $item['name'])->first();

            if ($newItem === null) {
                $newItem = Wharehouse::create([
                    'name'         => $item['name'],
                    'level'        => $item['level'],
                ]);
            }
        }
    }
}

Migration I used:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateWarehouseTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('warehouses', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name')->unique();
            $table->integer('level')->nullable();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('warehouses');
    }
}

Change how null value is sorted using orderBy()

Simple put a - sign before the column

public function sort($builder, $direction = 'asc')
{
    return $builder->orderBy('-table.nullable_column', $direction);
}

reference: MySQL Orderby a number, Nulls last

Sorting laravel collection by leaving null/empty last

Try:

$collection->sortBy('-timestamp')


Does it work?

---

Related Topics