How to Use Order By for Multiple Columns in Laravel 4?

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?

Solution 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

Solution 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 :)

Solution 3

Use order by like this:

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

Solution 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);

Solution 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();

View more solutions

Comments

[I am the Most Stupid Person]

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

[JoshuaDavid]

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

[rmobis]

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

[Rafael]

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

[rmobis]

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

[ssi-anik]

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

[Return-1]

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

[Sagar Naliyapara]

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

[Return-1]

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

[Sagar Naliyapara]

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

[Ray Coder]

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? @rmobis

[rmobis]

@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].

[endo64]

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

[Mohamed Raza]

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