Eloquent: Sorting rows but empty values at the end/beginning

Introduction

When we deal with databases, we often need to retrieve data in a specific order. Typically, we want to see vital entries first — meaning rows with actual values that are relevant to the user’s query. However, what happens when some rows have empty values? Instead of mixing these with the rest, a polished design would put those entries at the end or beginning of results, depending on the sort order. In this tutorial, we will explore how to implement this using Eloquent in Laravel.

Let’s dive into how we can precisely control the order of records retrieved from a database when using Laravel’s Eloquent ORM, and carefully handle rows with empty or null values.

Understanding the Basics

Before we jump into ordering data with non-null precedence, it’s crucial to understand how sorting works in Eloquent. The orderBy() clause is used to sort results by a specified column. For instance, User::orderBy('name')->get() returns users sorted alphabetically by name.

$users = User::orderBy('name')->get();

What if we want to sort by a column that might have empty (NULL) values? By default, SQL databases sort NULL values as lower than any non-NULL value when ordering in ascending order. The reverse is true for descending order.

Sorting with Custom Orderings Using CASE

A commonly used technique is leveraging the SQL CASE statement. With Eloquent, this involves using raw expressions. Here’s an example where empty values in the ‘name’ column are sorted to come last, regardless of whether sorting in ascending or descending order.

$users = User::select('*', 
                   \DB::raw("CASE WHEN name IS NULL THEN 1 ELSE 0 END as is_null"))
              ->orderBy('is_null')
              ->orderBy('name', 'asc')
              ->get();

Using Subqueries to Influence Sort Order

Subqueries can also be used to control sort order in Eloquent. We can order our primary query based on the results from a subquery, which gives us control over how NULL values are treated.

$users = User::orderByDesc(
             User::select('created_at')
                 ->whereColumn('users.id', 'user_details.user_id')
                 ->orderBy('created_at', 'desc')
                 ->limit(1)
         )
         ->get();

Here, we are sorting the users based on their latest activity date, but if the date is NULL, these users will be sorted as if they were the oldest.

Applying Conditional Sort Order with Advanced Eloquent Queries

Taking it a step further, advance your queries using Eloquent’s query builder’s orderByRaw() method to write a more complex ORDER BY expression.

$users = User::orderByRaw('ISNULL(name), name ASC')->get();

This uses the MySQL ISNULL() function to create a boolean condition, ensuring NULL values take the lowest precedence.

Leveraging Scopes for Reusability

Eloquent allows the use of local scopes to encapsulate query logic and make it reusable. We can define a scope in our model that encapsulates our custom sort logic. Whenever you need to apply this particular ordering, you can simply use your new scope instead of repeating the logic.

class User extends Model
{
    public function scopeOrderByNullLast($query, $column, $direction = 'asc')
    {
        $query->orderByRaw((new static)->getNullLastOrder($column, $direction));
    }

    protected function getNullLastOrder($column, $direction)
    {
        // Assuming MySQL is used
        $direction = strtolower($direction) == 'asc' ? 'ASC' : 'DESC';
        return "ISNULL($column), $column $direction";
    }
}

// You can then use the scope like this:
$users = User::orderByNullLast('name')->get();

Conclusion

Ensuring that rows with empty values are sorted appropriately is vital for providing clear and user-friendly data presentation. In Laravel’s Eloquent ORM, you have the flexibility to implement sophisticated sorting strategies that respect the spatial logic of your data. This tutorial covered several methods, from basic CASE statements to advanced subqueries, that you can use to tackle your application’s unique needs when it comes to sorting and presenting data.

With these techniques and by leveraging the power of scopes for added elegance and reusability, your Eloquent queries can ensure meaningful and intuitive data ordering for your users, even when dealing with the challenge of empty values.