What is the syntax for sorting an Eloquent collection by multiple columns?

Question

I know that when using the query builder, it is possible to sort by multiple columns using

...orderBy('column1')->orderBy('column2')

but now I am dealing with a collection object. Collections have the sortBy method, but I have not been able to figure out how to make it work for multiple columns. Intuitively, I initially tried to use the same syntax as orderBy.

sortBy('column1')->sortBy('column2)

but this apparently just applies the sorts sequentially and it ends up sorted by column2, disregarding column1. I tried

sortBy('column1', 'column2')

but that throws the error "asort() expects parameter 2 to be long, string given". Using

sortBy('column1, column2')

doesn't throw an error, but the sort appears to be pretty random, so I don't really know what that actually does. I looked at the code for the sortBy method, but unfortunately I am having a hard time understanding how it works.

Answer 1

sortBy() takes a closure, allowing you to provide a single value that should be used for sorting comparisons, but you can make it a composite by concatenating several properties together

$posts = $posts->sortBy(function($post) {
    return sprintf('%-12s%s', $post->column1, $post->column2);
});

If you need the sortBy against multiple columns, you probably need to space pad them to ensure that "ABC" and "DEF" comes after "AB" and "DEF", hence the sprint right padded for each column up to the column's length (at least for all but the last column)

Note that it's generally a lot more efficient if you can use an orderBy in your query so the collection is ready-sorted on retrieval from the database

Answer 2

I found a different way to do this using sort() on the eloquent Collection. It may potentially work a bit better or at least be a bit easier to understand than padding the fields. This one has more comparisons but I'm not doing the sprintf() for every item.

$items = $items->sort(
    function ($a, $b) {
        // sort by column1 first, then 2, and so on
        return strcmp($a->column1, $b->column1)
            ?: strcmp($a->column2, $b->column2)
            ?: strcmp($a->column3, $b->column3);
    }
);

Answer 3

As @derekaug mentioned, the sort method allows us to enter a custom closure for sorting the collection. But I thought his solution was somewhat cumbersome to write and it woulde be nice to have something like this:

$collection = collect([/* items */])
$sort = ["column1" => "asc", "column2" => "desc"];
$comparer = $makeComparer($sort);
$collection->sort($comparer);

In fact, this can be easily archived by the following $makeComparer wrapper to generate the compare closure:

$makeComparer = function($criteria) {
  $comparer = function ($first, $second) use ($criteria) {
    foreach ($criteria as $key => $orderType) {
      // normalize sort direction
      $orderType = strtolower($orderType);
      if ($first[$key] < $second[$key]) {
        return $orderType === "asc" ? -1 : 1;
      } else if ($first[$key] > $second[$key]) {
        return $orderType === "asc" ? 1 : -1;
      }
    }
    // all elements were equal
    return 0;
  };
  return $comparer;
};

Examples

$collection = collect([
  ["id" => 1, "name" => "Pascal", "age" => "15"],
  ["id" => 5, "name" => "Mark", "age" => "25"],
  ["id" => 3, "name" => "Hugo", "age" => "55"],
  ["id" => 2, "name" => "Angus", "age" => "25"]
]);

$criteria = ["age" => "desc", "id" => "desc"];
$comparer = $makeComparer($criteria);
$sorted = $collection->sort($comparer);
$actual = $sorted->values()->toArray();

/**
* [
*  ["id" => 5, "name" => "Hugo", "age" => "55"],
*  ["id" => 3, "name" => "Mark", "age" => "25"],
*  ["id" => 2, "name" => "Angus", "age" => "25"],
*  ["id" => 1, "name" => "Pascal", "age" => "15"],
* ];
*/

$criteria = ["age" => "desc", "id" => "asc"];
$comparer = $makeComparer($criteria);
$sorted = $collection->sort($comparer);
$actual = $sorted->values()->toArray();

/**
* [
*  ["id" => 5, "name" => "Hugo", "age" => "55"],
*  ["id" => 2, "name" => "Angus", "age" => "25"],
*  ["id" => 3, "name" => "Mark", "age" => "25"],
*  ["id" => 1, "name" => "Pascal", "age" => "15"],
* ];
*/

$criteria = ["id" => "asc"];
$comparer = $makeComparer($criteria);
$sorted = $collection->sort($comparer);
$actual = $sorted->values()->toArray();

/**
* [
*  ["id" => 1, "name" => "Pascal", "age" => "15"],
*  ["id" => 2, "name" => "Angus", "age" => "25"],
*  ["id" => 3, "name" => "Mark", "age" => "25"],
*  ["id" => 5, "name" => "Hugo", "age" => "55"],
* ];
*/

Now, since we're talking Eloquent here, chances are high that you're also using Laravel. So we might even bind the $makeComparer() closure to the IOC and resolve it from there:

// app/Providers/AppServiceProvider.php 
// in Laravel 5.1
class AppServiceProvider extends ServiceProvider
{
    /**
     * ...
     */


    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        $this->app->bind("collection.multiSort", function ($app, $criteria){
                return function ($first, $second) use ($criteria) {
                    foreach ($criteria as $key => $orderType) {
                        // normalize sort direction
                        $orderType = strtolower($orderType);
                        if ($first[$key] < $second[$key]) {
                            return $orderType === "asc" ? -1 : 1;
                        } else if ($first[$key] > $second[$key]) {
                            return $orderType === "asc" ? 1 : -1;
                        }
                    }
                    // all elements were equal
                    return 0;
                };
        });
    }
}

Now you can use it everywhere you need to like so:

$criteria = ["id" => "asc"];
$comparer = $this->app->make("collection.multiSort",$criteria);
$sorted = $collection->sort($comparer);
$actual = $sorted->values()->toArray();

Answer 4

A simple solution is to chain sortBy() multiple times in reverse order of how you want them sorted. Downside is this is likely to be slower than sorting at once in the same callback, so use at your own risk on large collections.

$collection->sortBy('column3')->sortBy('column2')->sortBy('column1');