How to Use OrderBy for Multiple Columns in Laravel?

The ORDERBY clause is used to arrange the columns in a table in ascending or descending order. By default it sorts the columns in ascending order, if you need to sort in descending order you should use DSC along with the clause.

Syntax

Following is the syntax of this statement −

SELECT column1, column2, ... 
FROM table_name 
ORDER BY column1, column2, ... ASC|DESC;

Assume we have created a table named Students in MySQL database using the following query −

CREATE TABLE students(
   id           INTEGER      NOT  NULL   PRIMARY   KEY,
   name         VARCHAR(10)  NOT NULL,
   email        VARCHAR(15)  NOT NULL,
   created_at   VARCHAR(27)  NOT NULL,
   updated_at   VARCHAR(27)  NOT NULL,
   address      VARCHAR(3)   NOT NULL
);

And populated it using the following INSERT statements −

INSERT INTO students(id,name,email,created_at,updated_at,address,age) VALUES (1,'Siya Khan','siya@gmail.com','2022-05-01T13:45:55.000000Z','2022-05-01T13:45:55.000000Z','Xyz', 20);

INSERT INTO students(id,name,email,created_at,updated_at,address,age) VALUES (2,'Rehan Khan','rehan@gmail.com','2022-05-01T13:49:50.000000Z','2022-05-01T13:49:50.000000Z','Xyz', 18);

INSERT INTO students(id,name,email,address,age) VALUES (3,'Rehan Khan','rehan@gmail.com','testing',20);

INSERT INTO students(id,name,email,address,age) VALUES (4,'Rehan','rehan@gmail.com','abcd',15);

INSERT INTO students(id,name,email,address,age) VALUES (5,'Nidhi Agarwal','nidhi@gmail.com','abcd',20);

INSERT INTO students(id,name,email,address,age) VALUES (6,'Ashvik Khanna','ashvik@gmail.com','oooo',16);

INSERT INTO students(id,name,email,address,age) VALUES (7,'Viraj Desai','viraj@gmail.com','test',18);

INSERT INTO students(id,name,email,address,age) VALUES (8,'Priya Singh','priya@gmail.com','test123',20);

If you retrieve the created table, it will look like this −

+----+---------------+------------------+-----------------------------+-----------------------------+---------+------+
| id |       name    |        email     |              created_at     |              updated_at     |address  | age  |
+----+---------------+------------------+-----------------------------+-----------------------------+---------+------+
|  1 |     Siya Khan | siya@gmail.com   | 2022-05-01T13:45:55.000000Z | 2022-05-01T13:45:55.000000Z | Xyz     | 20   |
|  2 |    Rehan Khan | rehan@gmail.com  | 2022-05-01T13:49:50.000000Z | 2022-05-01T13:49:50.000000Z | Xyz     | 18   |
|  3 |    Rehan Khan | rehan@gmail.com  | NULL                        | NULL                        | testing | 20   |
|  4 |         Rehan | rehan@gmail.com  | NULL                        | NULL                        | abcd    | 15   |
|  5 | Nidhi Agarwal | nidhi@gmail.com  | NULL                        | NULL                        | abcd    | 20   |
|  6 | Ashvik Khanna | ashvik@gmail.com | NULL                        | NULL                        | oooo    | 16   |
|  7 |   Viraj Desai | viraj@gmail.com  | NULL                        | NULL                        | test    | 18   |
|  8 |   Priya Singh | priya@gmail.com  | NULL                        | NULL                        | test123 | 20   |
+----+---------------+------------------+-----------------------------+-----------------------------+---------+------+
8 rows in set (0.00 sec)

In the article we are going to make use of eloquent model student so to use orderBy the syntax will be −

Student::orderBy();

Following is the MySQL query to invoke order by on multiple columns −

SELECT * FROM 'students' ORDER BY fieldname ASC/DESC

Example 1

Following program retrieves multiple columns of a table using the ORDERBY clause −

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Student;

class StudentController extends Controller {
   public function index() {
      echo $student = Student::orderBy('name', 'DESC')
      ->orderBy('email', 'ASC')
      ->get();
   }
}

Output

The output for above one is −

[{"id":1,"name":"Siya Khan","email":"siya@gmail.com","address":"Xyz"},
{"id":2,"name":"Rehan Khan","email":"rehan@gmail.com","address":"Xyz"}]

Query used

The select query for above one is −

SELECT * FROM 'students' ORDER BY 'name' DESC, 'email' ASC

If you execute above query in MySQL you get below output

mysql> SELECT * FROM students ORDER BY 'name' DESC, 'email' ASC;
+----+------------+-----------------+-----------------------------+-----------------------------+---------+
| id | name       |          email  | created_at                  |        updated_at           | address |
+----+------------+-----------------+-----------------------------+-----------------------------+---------+
| 1  | Siya Khan  | siya@gmail.com  | 2022-05-01T13:45:55.000000Z | 2022-05-01T13:45:55.000000Z | Xyz     |
| 2  | Rehan Khan | rehan@gmail.com | 2022-05-01T13:49:50.000000Z | 2022-05-01T13:49:50.000000Z | Xyz     |
+----+------------+-----------------+-----------------------------+-----------------------------+---------+
2 rows in set (0.00 sec)

Example 2

Following is another example demonstrating the use of ORDERBY clause in Laravel. Here, the orderBy given on name and email is ASC −

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Student;

class StudentController extends Controller {
   public function index() {
      echo $student = Student::orderBy('name', 'ASC')
      ->orderBy('email', 'ASC')
      ->get();
   }
}

Output

The output for above is −

[{"id":2,"name":"Rehan Khan","email":"rehan@gmail.com","created_at":"2022-05-01T13:49:50.000000Z","updated_at":"2022-05-01T13:49:50.000000Z","address":"Xyz"},{"id":1,"name":"Siya Khan","email":"siya@gmail.com","created_at":"2022-05-01T13:45:55.000000Z","updated_at":"2022-05-01T13:45:55.000000Z","address":"Xyz"}]

Query used

The query for above case is −

SELECT * FROM 'students' ORDER BY 'name' ASC, 'email' ASC;

The output when executed in MySQL is as follows

mysql> SELECT * FROM students ORDER BY name ASC, email ASC;
+----+------------+-----------------+-----------------------------+-----------------------------+---------+
| id | name       | email           | created_at                  | updated_at                  | address |
+----+------------+-----------------+-----------------------------+-----------------------------+---------+
| 2  | Rehan Khan | rehan@gmail.com | 2022-05-01T13:49:50.000000Z | 2022-05-01T13:49:50.000000Z | Xyz     |
| 1  | Siya Khan  | siya@gmail.com  | 2022-05-01T13:45:55.000000Z | 2022-05-01T13:45:55.000000Z | Xyz     |
+----+------------+-----------------+-----------------------------+-----------------------------+---------+
2 rows in set (0.00 sec)