How to sort NULL values last using Eloquent in Laravel

Question

I've got a many to many relationship between my employees and groups table. I've created the pivot table, and all is working correctly with that. However, I've got a sortOrder column on my employees table that I use to determine the order in which they display. Employee with a value of 1 in the sortOrder column should be first, value of 2 should be second, so on. (Or backwards if sorted descending) The sortOrder column is a integer column that allows null values.

I've set up my group model to sort the employees by the sort column, but I've run into a problem. The null values always are displayed first. I've tried using ISNULL and similar SQL methods in place of the regular "asc" or "desc" used, but I only get an error.

Here's the code in my Group model:

class Group extends Eloquent {

public function employees()
    {
        return $this->belongsToMany("Employee")->orderBy('sortOrder', 'asc');
    }
}

And here's what I use in the controller to access my model:

$board = Group::find(6)->employees;

What's the trick in Laravel to sorting NULL values last?

Answer 1

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

Answer 2

Just add a minus sign to field and change order to DESC.

$q->orderBy(\DB::raw('-`sortOrder`'), 'desc');

Answer 3

In Laravel 5.2 or higher just call orderByRaw. You even able to sort through aggregated value rather than a column. In the following example max_st can be null if there is no submodels.

Model::where('act', '2')
    ->leftJoin('submodels', 'model.id', '=', 'submodels.model_id')
    ->select('models.*', DB::raw('MAX(submodels.st) as max_st')),
    ->orderByRaw('max_st DESC NULLS LAST');

Answer 4

public function employees()
{
    return $this
        ->hasMany('Employee')
        ->select(['*', DB::raw('IF(`sortOrder` IS NOT NULL, `sortOrder`, 1000000) `sortOrder`')])
        ->orderBy('sortOrder', 'asc');
}

Explanation:
The IF statement solves the issue here. If NULL value is found, some big number is assigned to sortOrder instead. If found not NULL value, real value is used.

Answer 5

I ran into this problem recently using Laravel 5.6, where junkystu answer was perfect for me. However our testing framework uses sqlite, so tests were constantly returning a 500 error.

This is what we came up with, which should be slightly more agnostic of a DB driver.

Ascending

$query->orderBy(DB::raw('column_to_sort IS NULL, column_to_sort'), 'asc');

Descending

$query->orderBy(DB::raw('column_to_sort IS NOT NULL, column_to_sort'), 'desc');

Answer 6

Instead of relying on an arbitrary large number you can also do:

public function employees()
{
    return $this
        ->hasMany('Employee')
        ->select(['*', DB::raw('sortOrder IS NULL AS sortOrderNull')])
        ->orderBy('sortOrderNull')
        ->orderBy('sortOrder');
}

It has an added benefit of being supported by SQLite.

Answer 7

More elegantly you can do as below for better results

1. This will order latest to oldest and null to the last.

    ->orderByRaw("CASE WHEN column_to_order IS NULL THEN 0 ELSE 1 END DESC")
    ->orderBy('column_to_order', 'DESC')
   
   

2. This will arrange null records first and then oldest to latest.

   ->orderByRaw("CASE WHEN column_to_order IS NULL THEN 0 ELSE 1 END ASC")
   ->orderBy('column_to_order', 'ASC')

Answer 8

A workaround for PostgreSQL

For numeric types:

DB::table('t')
    ->select(['id', 'val'])
    ->orderBy(DB::raw("coalesce(val, 0)"), 'desc')

For text types:

orderBy(DB::raw("coalesce(val, '')"), 'desc')

The trick is to replace NULL values in the sorting column to zero (or empty string) so that it could be sorted as an ordinary integer (or text) value.

Answer 9

For Nested Relationship

Nowhere did I find the solution to do the same thing for ordering in a nested relationship. Had to improvise and this is what I came up with:

// Sorting order
$order = 'asc'

// Order Products by $product->store->manager?->name
Product::orderByRaw(
    "(". 
        Manager::select('name')
            ->whereHas('store', function (Builder $q) {
                $q->whereColumn('id', 'products.store_id');
            })
            ->toSql() 
    .") $order NULLS LAST"
);

EDIT
OK, I found the query above to be slow, so I rewrote it like this:

$query = Product::query()
    ->leftJoin('stores', 'stores.id', 'products.store_id')
    ->leftJoin('managers', 'managers.id', 'stores.manager_id')
    ->orderByRaw("managers.name $order NULLS LAST");

Joins worked 3 times faster than subqueries. But be aware that you'll need to add table prefixes (such as 'products.title') to all your further Eloquent calls on this $query.

Answer 10

If anyone else needs sortBy using null last for Laravel collection can use this trick,

collect([
    ['order' => null],
    ['order' => 1],
    ['order' => 44],
    ['order' => 4],
    ['order' => null],
    ['order' => 2],
])
    ->sortBy('order')
    ->sortBy(fn($item, $key) => $item['order'] == null);

Answer 11

->orderBy('sortOrder', 'is', 'null')->orderBy('sortOrder', 'asc')

Seems to work.