2

Laravel's Collection class (v5.5) has a sortBy() method that sorts everything similar to if you had used a SQL ORDER BY statement, except there is one striking difference: a relational database will put NULL values at the end when sorting ascending (or at least PostgreSQL does), but Laravel's sortBy() method puts NULL values first.

So how do I get those NULL values to be sorted last instead of first when using the Collection::sortBy() method? PLEASE NOTE: I cannot change the database query itself! MY ONLY OPTION is to sort the Collection itself within PHP. I absolutely cannot do this at the database level in my situation.

There is a similar question on Stack Overflow here but the solution OP found was kind of a hack and does not work in my situation, because I need it to work for varchars.

NOTE: I realize sortBy() accepts a Closure for the first argument but there is zero explanation in the documentation about the arguments this closure receives (which "key" is $key?), nor does it explicitly say what the closure is supposed to return in order to determine the sort order. (I'm assuming it should return an integer representing the order, but I do not know how to make that work for me with multiple NULL values.)

fronzee
  • 1,668
  • 2
  • 21
  • 32

1 Answers1

2

The sortBy method accepts a field on which to sort, in ascending order. So if you had a collection of App\User objects, you could pass in say first_name and that would sort by each user's first name.

If your logic is more complex, and you wanted to sort on something that isn't strictly a field of each item in your collection you may pass a closure instead. The first parameter passed to the closure is the actual item. You should return a value that you want to be sorted from the closure. Let's say in your collection of App\User objects, you wanted to sort by the last letter of each person's first name:

$users->sortBy(function ($item) {
    return substr($item->first_name, -1);
});

The second parameter is the key, which is the key of the collection, or the underlying array it represents. If you've retrieved a collection from the database, this will likely be a numeric index, but if you had a different collection or you decided to re-key the collection by say, the user's email address (Using keyBy), then that is what is passed.

When it comes to sticking all of your null values at the end of the sorted result set, I would suggest using the sort method instead of sortBy. When you pass a closure to sort, it accepts two items, representing two items from your collection to be sorted. In the case of your collection of App\User objects, each item would an instance of App\User. This gives you total control on how two objects are compared and therefore total control over the order.

You should return -1 (or a negative value) if the first item is considered to be less than the second, you should return 0 if the two items are to be considered equal and 1 (or a positive value) if the first item is considered to be greater than the second.

$users->sort(function ($a, $b) {
    // Return -1, 0 or 1 here
});

That should allow you to implement your logic. To ensure null values are moved to the end, just return 1 every time whatever you're interested in from $a is null. Similarly, if whatever you're interested in from $b is null, return -1 and if whatever you're interested in from both $a and $b are null, return 0.

Jonathon
  • 15,873
  • 11
  • 73
  • 92