How to sort a Laravel query builder result by multiple columns?

Question

I want to sort multiple columns in Laravel 4 by using the method orderBy() in Laravel Eloquent. The query will be generated using Eloquent like this:

SELECT *
FROM mytable
ORDER BY
  coloumn1 DESC, coloumn2 ASC

How can I do this?

Arela

Very simple. User::orderBy('name', 'DESC') ->orderBy('email', 'ASC') ->get(); – Offcolor

Answer 1

Simply invoke orderBy() as many times as you need it. For instance:

User::orderBy('name', 'DESC')
    ->orderBy('email', 'ASC')
    ->get();

Produces the following query:

SELECT * FROM `users` ORDER BY `name` DESC, `email` ASC

Mailemailed

It would be nice if we could pass an array like: User::orderBy(array('name'=>'desc', 'email'=>'asc')) – Less

@FireCoding, you can do $user->orders = array(array('column' => 'name', 'direction' => 'desc'), array('column' => 'email', 'direction' => 'asc')); – Mailemailed

Is there a way to add an orderBy on an established query? – Cussedness

@Rafael, if you haven't already ran it (called get or first), just call orderBy on it. Else, nope. – Mailemailed

Otherwise, if you always need to order by desc, then you can use latest() too. – Henshaw

In this article lavalite.org/blog/using-orderby-for-multiple-columns-laravel said we can use User::orderBy(array('name'=>'desc', 'email'=>'asc')). But i got error stripos() expects parameter 1 to be string, array given. Why? @Mailemailed – Tsar

@RayCoder I wouldn't know. Neither the method I posted here nor the one on the article seem to be present on the official documentation and the API page for Laravel 4 (which is where I found it at first) is no longer up. However, my guess would be that the article's method doesn't work, and it couldn't. Because orderBy needs to know the order of the columns it was given, and it can't do that when you use string keys, as ['a' => 1, 'b' => 1] is the same as ['b' => 1, 'a' => 1]. – Mailemailed

Answer 2

You can do as @rmobis has specified in his answer, [Adding something more into it]

Using order by twice:

MyTable::orderBy('coloumn1', 'DESC')
    ->orderBy('coloumn2', 'ASC')
    ->get();

and the second way to do it is,

Using raw order by:

MyTable::orderByRaw("coloumn1 DESC, coloumn2 ASC");
    ->get();

Both will produce same query as follow,

SELECT * FROM `my_tables` ORDER BY `coloumn1` DESC, `coloumn2` ASC

As @rmobis specified in comment of first answer you can pass like an array to order by column like this,

$myTable->orders = array(
    array('column' => 'coloumn1', 'direction' => 'desc'), 
    array('column' => 'coloumn2', 'direction' => 'asc')
);

one more way to do it is iterate in loop,

$query = DB::table('my_tables');

foreach ($request->get('order_by_columns') as $column => $direction) {
    $query->orderBy($column, $direction);
}

$results = $query->get();

Hope it helps :)

Gambetta

can i used orderByRaw AND orderBy together? doesnt seem to work for me, the resulting query only seems to respect orderByRaw – Expectoration

try to put orderBy first and then orderByRaw and see the result @GeorgeAvgoustis – Gambetta

unfortunately this cannot be done as it first needs to be randomized and then order by the final qualifier. – Expectoration

It does work together might be because applying on first column sorting you can not see second column sorting – Gambetta

Answer 3

Use order by like this:

return User::orderBy('name', 'DESC')
    ->orderBy('surname', 'DESC')
    ->orderBy('email', 'DESC')
    ...
    ->get();

Infrequency

That is what I do , but in my case I would like that if two users have the same surname, the corresponding entries respect the naming order . Any Ideas? – Almeria

@Berni, the order you add your orderBy it should get executed. So, order by surname asc, name desc or something like that should be in your query. so your eloquent should be written in that order as well! – Henshaw

Answer 4

Here's another dodge that I came up with for my base repository class where I needed to order by an arbitrary number of columns:

public function findAll(array $where = [], array $with = [], array $orderBy = [], int $limit = 10)
{
    $result = $this->model->with($with);
    $dataSet = $result->where($where)
        // Conditionally use $orderBy if not empty
        ->when(!empty($orderBy), function ($query) use ($orderBy) {
            // Break $orderBy into pairs
            $pairs = array_chunk($orderBy, 2);
            // Iterate over the pairs
            foreach ($pairs as $pair) {
                // Use the 'splat' to turn the pair into two arguments
                $query->orderBy(...$pair);
            }
        })
        ->paginate($limit)
        ->appends(Input::except('page'));

    return $dataSet;
}

Now, you can make your call like this:

$allUsers = $userRepository->findAll([], [], ['name', 'DESC', 'email', 'ASC'], 100);

America

Answer 5

$this->data['user_posts'] = User_posts::with(['likes', 'comments' => function($query) { $query->orderBy('created_at', 'DESC'); }])->where('status', 1)->orderBy('created_at', 'DESC')->get();

Sinuate

Code only answers are discourage on SO, please add some explanation. – Idiomatic

the method you posted is not working. will you be able to explain a bit – Mckee