When working with databases, it's common to encounter situations where some fields might be empty or NULL. One challenge that often arises is how to sort records so that rows with empty fields appear at the bottom of the result set, while those with non-empty values are ordered in a meaningful way (e.g., descending order). In this post, I'll show you how to achieve this in Laravel using a practical example.

Scenario

Let's say you have a table of participants for an activity, and each participant may or may not have an attended_at timestamp, which records when the participant attended the activity. You want to list all participants, sorting them in such a way that:

• Participants who have attended (i.e., attended_at is not empty) appear first, sorted by the most recent attendance.
• Participants who have not attended (i.e., attended_at is empty or NULL) appear last.

The Problem

By default, Laravel's latest() method sorts records by a given column in descending order. However, it doesn't handle the case where some records have NULL or empty values. If you simply use:

return $this
    ->participants()
    ->with('user')
    ->latest('attended_at')
    ->get();

It will order the participants with a timestamp, but it won’t move the NULL values to the bottom.

The Solution: Using orderByRaw

We can achieve the desired sorting using the orderByRaw() method. This allows us to write custom sorting logic using raw SQL expressions. In this case, we’ll order by whether attended_at is NULL, and then order by attended_at in descending order for non-NULL values.

Here’s how you can write this query in Laravel:

return $this
    ->participants()
    ->with('user')
    ->orderByRaw('attended_at IS NULL, attended_at DESC')
    ->get()
    ->map(function (ActivityParticipant $participant) {
        return [
            'metric_number' => data_get($participant, 'user.student_id'),
            'name' => data_get($participant, 'user.name'),
            'status' => data_get($participant, 'status'),
            'attended_at' => data_get($participant, 'attended_at', '-'),
        ];
    })->toArray();

Breaking Down the Query

• orderByRaw('attended_at IS NULL, attended_at DESC'):
  • attended_at IS NULL: This will return true (or 1) for rows where attended_at is NULL and false (or 0) otherwise. This effectively places NULL values at the bottom.
  • attended_at DESC: After handling the NULL values, this sorts the remaining rows with non-empty attended_at values in descending order, meaning the most recent attendance will be shown first.

Final Output

The map() function is then used to format the output in the desired structure, including fields such as the student_id, name, and status, and converting the attended_at timestamp to a human-readable format or '-' if it is empty.

Benefits of This Approach

• Performance: This approach leverages SQL’s sorting capabilities, making it efficient for large datasets.
• Readability: Using orderByRaw() with a raw SQL expression keeps the code concise and easy to understand.
• Flexibility: You can easily extend this logic to sort by other fields or include more complex conditions.

Conclusion

Handling sorting with NULL or empty values in Laravel is a common task when working with data that may not always be complete. By using the orderByRaw() method with a SQL expression like attended_at IS NULL, attended_at DESC, you can easily ensure that rows with NULL values appear at the bottom while still sorting the rest of the data in a meaningful way.

This method is especially useful in scenarios where you're dealing with attendance, as shown in this example, but it can be applied to many other situations where some data fields may be missing. With this approach, you can provide your users with a clearer and more intuitive display of records in your application.

Happy coding!