How to sort NULL values last using Eloquent in Laravel

How to sort NULL values last using Eloquent in Laravel

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

How to sort NULL values last using Eloquent in Laravel

When managing collections of related data, particularly within many-to-many relationships, maintaining a specific display order is often crucial. In your scenario, you have an employees table with an integer sortOrder column intended to dictate the sequence in which team members appear on a board. You've successfully established the relationship and are fetching it via $board = Group::find(6)->employees. However, the presence of nullable integers introduces a specific sorting challenge: standard SQL sorts NULL values first by default, regardless of whether you specify ASC or DESC.

In your current implementation using orderBy('sortOrder', 'asc'), Laravel generates a query like this:

SELECT * FROM employees WHERE employee_id IN (6) ORDER BY sort_order ASC

Because NULL is treated as the lowest possible value in numeric sorting, all employees without a defined order appear at the very top of your list. Simply using SQL functions like ISNULL or COALESCE directly inside the Eloquent orderBy method often fails because these functions expect to be applied to specific columns in a way that standard query builders handle differently, and they can lead to syntax errors if not wrapped correctly within raw expressions.

To resolve this, you need to instruct the database engine to treat NULL as a higher value than any existing integer. The most robust approach in Laravel is to use a conditional expression within the orderBy method. This allows you to sort by zero (or a negative number) for non-null values and then by one (or a positive number) for nulls.

You can achieve this by using a closure with orderByRaw or by leveraging the query builder's ability to execute raw expressions. Here is how you can modify your employees relationship in the Group model:

public function employees()
{
    return $this->belongsToMany('Employee')
        ->orderByRaw("COALESCE(sortOrder, 9999) ASC");
}

This solution relies on the COALESCE function. If sortOrder contains a valid number (e.g., 1, 2, 5), COALESCE returns that number. If sortOrder is NULL, COALESCE substitutes it with 9999. Consequently, all employees with defined sort orders will appear first in ascending order, followed by those where the column is null.

Alternatively, if you prefer a more explicit conditional logic or want to handle descending sorts differently, you can use a custom raw expression:

->orderByRaw("CASE WHEN sortOrder IS NULL THEN 9999 ELSE sortOrder END ASC")

This CASE statement explicitly checks for nulls and assigns them a high priority value. This pattern is highly portable across most SQL databases supported by Laravel, including MySQL, PostgreSQL, and SQLite.

Why not just use NULLS LAST?

If you were working with PostgreSQL or Oracle, you might be tempted to use the NULLS LAST syntax. While this is a valid SQL standard feature, relying on it can introduce portability issues if your application ever needs to support other database engines like MySQL or SQLite, which do not natively support this specific clause in older versions. Using COALESCE or a CASE statement ensures your sorting logic remains consistent across different environments without requiring version-specific configuration.

Implementation Considerations

When implementing this change, ensure that the fallback value you choose (like 9999) is actually higher than any realistic maximum value for your sortOrder column. If your application allows sort orders up to 10,000, using 9999 would cause nulls to appear in the middle of the list rather than at the end. Adjust the constant or use a dynamic approach based on your schema's constraints.

By shifting from a simple string column name to a raw expression that accounts for nullability, you preserve the intended user experience: defined orders dictate the layout, while undefined orders gracefully fall to the bottom. For further reading on advanced relationship queries and query builder nuances, resources like https://laravelcompany.com can provide additional context on optimizing Eloquent relationships in production environments.

Our network