- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)