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?
5 Answers
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
7 Comments
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.
|
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 :)
4 Comments
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
Use order by like this:
return User::orderBy('name', 'DESC')
->orderBy('surname', 'DESC')
->orderBy('email', 'DESC')
...
->get();
answered Jul 19, 2020 at 6:59
Hedayatullah Sarwary
2,8483 gold badges27 silver badges40 bronze badges
2 Comments
Berni
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?
ssi-anik
@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!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);
Comments
$this->data['user_posts'] = User_posts::with(['likes', 'comments' => function($query) { $query->orderBy('created_at', 'DESC'); }])->where('status', 1)->orderBy('created_at', 'DESC')->get();
User::orderBy('name', 'DESC') ->orderBy('email', 'ASC') ->get();