How to sort NULL values last using Eloquent in Laravel

Question

I've got a many-to-many relationship between my employees and the group 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. An employee with a value of 1 in the sortOrder column should be first, a value of 2 should be second, and so on. (Or backward if sorted descending) The sortOrder column is an 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?

Tilla

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

Elaina

This is working solution also for not numeric values, like dates. – Mirador

This is the least hacky solution IMHO. – Adamina

i tried but got mysql error. but fixed by adding ` in both sides of order like this 'ISNULL(`sortOrder`), `sortOrder` ASC' – Ovolo

Answer 2

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

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

Gonorrhea

For laravel 5.1: $q->orderBy(\DB::raw('-sortOrder'), 'desc'); – Dentist

For Laravel 5.4: $q->orderByRaw("-start_date",'DESC') – Decoder

And for Laravel 5.6: $query->orderByRaw('-start_date DESC') (note the single string instead of second parameter). – Gonfalonier

previous comment is wrong, should be $query->orderByRaw('-`start_date` DESC') – Programmer

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

Sibilant

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.

Speleology

Thanks! This works perfectly! I did not know that Fluent chain methods could be used in Eloquent. – Tilla

Could you tell me the importance of the brackets in the select? I'm having an odd problem with them. I have two development computers, and one of them has no problems with the brackets, the other one throws a FatalErrorException. I took them out, it works now on both computers, and my query result doesn't seem to have changed. – Tilla

Brackets passed to select() methods is nothing but new syntax for an Array. You experience the problem because this feature is new, comes with PHP 5.4. One of your computers run 5.4, the other doesn't. See php.net/manual/en/language.types.array.php or php.net/manual/en/migration54.new-features.php – Speleology

This looks like a bad practice. – Webb

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

Corycorybant

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.

Beverie

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

Xanthine

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.

Blackdamp

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.

Evangelistic

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

Avoid

Answer 11

This code will sort null values last:

$posts = Post::orderByRaw('ISNULL(`rank`), `rank` ASC')->get();

Kloman

Answer 12

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

Seems to work.

Teagan

Actually, that creates a query containing order by sortOrder DESC, sortOrder ASC which does not add NULL values last. – Elaina

@junkystu: How did you verify that? I haven't managed to get SQL logging working yet. I'm using Eloquent 5.0 btw. – Teagan

use the debug bar, it's very nice and even lets you see queries which are ran via ajax among other useful stuff – Elaina