如何在 Laravel 中对多列使用 OrderBy?
laravelphpserver side programming更新于 2025/7/4 6:37:17
ORDERBY 子句用于按升序或降序排列表中的列。默认情况下，它按升序对列进行排序，如果需要按降序排序，则应将 DSC 与该子句一起使用。
语法
以下是此语句的语法 -
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
假设我们已经使用以下查询在 MySQL 数据库中创建了一个名为 Students 的表 -
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 );
并使用以下 INSERT 语句填充它 -
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);
如果您检索创建的表，它将看起来像这样 -
+----+---------------+------------------+-----------------------------+-----------------------------+---------+------+ | 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)
在本文中，我们将使用 Eloquent 模型 Student，因此使用 orderBy 的语法如下:-
Student::orderBy();
以下是用于对多列调用 order by 的 MySQL 查询:-
SELECT * FROM 'students' ORDER BY fieldname ASC/DESC
示例 1
以下程序使用 ORDERBY 子句检索表的多列:-
<?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(); } }
输出
上述语句的输出为 −
[{"id":1,"name":"Siya Khan","email":"siya@gmail.com","address":"Xyz"},
{"id":2,"name":"Rehan Khan","email":"rehan@gmail.com","address":"Xyz"}]
使用的查询
上述语句的选择查询为 −
SELECT * FROM 'students' ORDER BY 'name' DESC, 'email' ASC
如果在 MySQL 中执行上述查询，将得到以下输出
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)
示例 2
以下是另一个在 Laravel 中演示如何使用 ORDERBY 子句的示例。此处，name 和 email 的 orderBy 为 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(); } }
输出
上面的输出是 −
[{"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"}]
使用的查询
上述案例的查询为 -
SELECT * FROM 'students' ORDER BY 'name' ASC, 'email' ASC;
在 MySQL 中执行后的输出如下
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)
