Laravel: Query Builder
Mục lục bài viết:
- Giới thiệu
- Chạy truy vấn cơ sở dữ liệu
- Thống kê, tập hợp
- Câu lệnh select
- Biểu thức raw
- Join
- Union
- Các mệnh đề where cơ bản
- Các mệnh đề where nâng cao
- Order by, Group by, limit và offset
- Phương thức when
- Phương thức insert
- Phương thức update
- Xóa dữ liệu bảng
- Khóa bi quan (Pessimistic Locking)
- Gỡ lỗi
Giới thiệu
Trình tạo truy vấn (Query builder) cơ sở dữ liệu của Laravel cung cấp một cách thức tiếp cận thân thiện để tạo và chạy các truy vấn cơ sở dữ liệu. Nó có thể được sử dụng để thực hiện hầu hết các hoạt động cơ sở dữ liệu trong ứng dụng của bạn và hoạt động hoàn hảo với tất cả các hệ thống cơ sở dữ liệu được hỗ trợ của Laravel.
Trình tạo truy vấn Laravel sử dụng liên kết tham số PDO để bảo vệ ứng dụng của bạn chống lại các cuộc tấn công chèn SQL (SQL injection). Bạn không cần phải làm sạch hoặc làm sạch các chuỗi được chuyển đến trình tạo truy vấn dưới dạng các ràng buộc truy vấn.
PDO không hỗ trợ tên cột ràng buộc. Do đó, bạn không bao giờ được phép cho phép người dùng nhập tên cột mà truy vấn của bạn tham chiếu, bao gồm cả các cột "order by".
Chạy truy vấn cơ sở dữ liệu
Lấy tất cả các hàng từ một bảng
Bạn có thể sử dụng phương thức table được cung cấp bởi facade DB để bắt đầu truy vấn. Phương thức table trả về một đối tượng query builder cho bảng nhất định, cho phép bạn đưa nhiều ràng buộc vào truy vấn và sau đó lấy lại kết quả của truy vấn bằng cách sử dụng phương thức get.
Ví dụ:
<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
class UserController extends Controller
{
public function index()
{
$users = DB::table('users')->get();
return view('user.index', ['users' => $users]);
}
}
Phương thức get trả về một đối tượng Illuminate\Support\Collection có chứa các kết quả của truy vấn trong đó mỗi kết quả là một thể hiện của đối tượng PHP stdClass. Bạn có thể truy cập giá trị của từng cột bằng cách truy cập cột đó như một thuộc tính của đối tượng.
Ví dụ:
use Illuminate\Support\Facades\DB;
$users = DB::table('users')->get();
foreach ($users as $user) {
echo $user->name;
}
Collection Laravel cung cấp nhiều phương thức cực kỳ mạnh mẽ để ánh xạ và thu nhỏ dữ liệu. Để biết thêm thông tin về collection, hãy xem tài liệu về collection.
Lấy một hàng/cột đơn từ bảng
Nếu bạn chỉ cần truy xuất một hàng từ bảng cơ sở dữ liệu, bạn có thể sử dụng phương thức first. Phương thức này sẽ trả về một đối tượng stdClass:
$user = DB::table('users')->where('name', 'John')->first();
return $user->email;
Nếu bạn không cần toàn bộ dữ liệu trên một hàng, bạn có thể trích xuất một giá trị từ một bản ghi bằng cách dùng phương thức value. Phương thức này sẽ trả về giá trị của cột tương ứng:
$email = DB::table('users')->where('name', 'John')->value('email');
Để truy xuất một hàng theo giá trị của cột id bạn hãy sử dụng phương thức find:
$user = DB::table('users')->find(3);
Truy xuất danh sách giá trị cột
Nếu bạn muốn truy xuất các giá trị của một cột, bạn có thể sử dụng phương thức pluck:
use Illuminate\Support\Facades\DB;
$titles = DB::table('users')->pluck('title');
foreach ($titles as $title) {
echo $title;
}
Bạn có thể chỉ định cột mà tập kết quả sẽ sử dụng làm khóa (key) của nó bằng cách cung cấp đối số thứ hai cho phương thức pluck:
$titles = DB::table('users')->pluck('title', 'name');
foreach ($titles as $name => $title) {
echo $title;
}
Phân chia kết quả
Nếu bạn cần làm việc với hàng nghìn bản ghi cơ sở dữ liệu, hãy xem xét sử dụng phương thức chunk. chunk sẽ truy xuất một phần nhỏ kết quả tại một thời điểm và đưa mỗi phần vào closure (hàm callback) để xử lý. Ví dụ sau đây truy xuất toàn bộ dữ liệu của bảng users nhưng với 100 bản ghi tại một thời điểm:
use Illuminate\Support\Facades\DB;
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
foreach ($users as $user) {
//
}
});
Bạn có thể ngừng xử lý các phần tiếp theo bằng cách trả về false như sau:
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
// Xử lý bản ghi
return false;
});
Nếu bạn đang cập nhật bản ghi cơ sở dữ liệu trong khi phân chia kết quả, thì kết quả phân chia của bạn có thể thay đổi theo những cách không mong muốn. Nếu bạn dự định cập nhật các bản ghi đã truy xuất trong khi phân chia, thì cách tốt nhất là bạn nên sử dụng phương thức chunkById để thay thế. Phương thức này sẽ tự động phân trang các kết quả dựa trên khóa chính của bản ghi:
DB::table('users')->where('active', false)
->chunkById(100, function ($users) {
foreach ($users as $user) {
DB::table('users')
->where('id', $user->id)
->update(['active' => true]);
}
});
Khi cập nhật hoặc xóa các bản ghi bên trong lệnh callback của chunk, thì bất kỳ thay đổi nào đối với khóa chính hoặc khóa ngoại có thể ảnh hưởng đến truy vấn chunk. Điều này có thể dẫn đến việc các bản ghi không được đưa vào các kết quả phân chia.
Thống kê, tập hợp
Query builder cũng cung cấp một loạt các phương thức để lấy giá trị tổng hợp như count, max, min, avg, và sum. Bạn có thể gọi bất kỳ phương thức nào sau đây sau khi tạo truy vấn của mình:
use Illuminate\Support\Facades\DB;
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
Tất nhiên, bạn có thể kết hợp các phương thức này với các mệnh đề khác để tinh chỉnh cách tính giá trị tổng hợp của bạn:
$price = DB::table('orders')
->where('finalized', 1)
->avg('price');
Xác định xem bản ghi có tồn tại không
Thay vì sử dụng phương thức count để xác định xem có tồn tại bất kỳ bản ghi nào phù hợp với các ràng buộc của truy vấn hay không, bạn có thể sử dụng các phương thức exists và doesntExist:
if (DB::table('orders')->where('finalized', 1)->exists()) {
// ...
}
if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
// ...
}
Câu lệnh select
Chỉ định một mệnh đề select
Không phải lúc nào bạn cũng có thể muốn chọn tất cả các cột từ bảng cơ sở dữ liệu. Sử dụng phương thức select bạn có thể chọn được những cột mong muốn một cách dễ dàng:
use Illuminate\Support\Facades\DB;
$users = DB::table('users')
->select('name', 'email as user_email')
->get();
Phương thức distinct cho phép bạn chỉ lấy một kết quả trong các kết quả giống nhau:
$users = DB::table('users')->distinct()->get();
Nếu bạn đã có một query builder rồi và bạn muốn thêm một cột vào kết quả thì bạn có thể sử dụng phương thức addSelect:
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();
Biểu thức raw
Đôi khi bạn có thể cần phải chèn một chuỗi tùy chỉnh vào một truy vấn. Để làm được điều này thì bạn có thể sử dụng phương thức raw:
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
Các câu lệnh raw sẽ được đưa vào truy vấn dưới dạng chuỗi, vì vậy bạn phải cực kỳ cẩn thận để tránh tạo lỗ hổng SQL injection.
Các phương thức raw
Thay vì sử dụng phương thức raw thì bạn cũng có thể sử dụng các phương thức sau để chèn một biểu thức thô vào các phần khác nhau của truy vấn của mình. Nhưng bạn cần lưu ý là Laravel không thể đảm bảo rằng bất kỳ truy vấn nào sử dụng biểu thức thô đều được bảo vệ khỏi các lỗ hổng SQL injection.
selectRaw
Phương thức selectRaw có thể được sử dụng thay cho addSelect(DB::raw(...)). Phương thức này chấp nhận một mảng ràng buộc tùy chọn làm đối số thứ hai của nó:
$orders = DB::table('orders')
->selectRaw('price * ? as price_with_tax', [1.0825])
->get();
whereRaw/orWhereRaw
Các phương thức whereRaw và orWhereRaw có thể được sử dụng để đưa mệnh đề "where" thô vào truy vấn của bạn. Các phương thức này chấp nhận một mảng ràng buộc tùy chọn làm đối số thứ hai:
$orders = DB::table('orders')
->whereRaw('price > IF(state = "TX", ?, 100)', [200])
->get();
havingRaw/orHavingRaw
Các phương thức havingRaw và orHavingRaw có thể được sử dụng để cung cấp một chuỗi thô làm giá trị của mệnh đề "having". Các phương thức này chấp nhận một mảng ràng buộc tùy chọn làm đối số thứ hai của chúng:
$orders = DB::table('orders')
->select('department', DB::raw('SUM(price) as total_sales'))
->groupBy('department')
->havingRaw('SUM(price) > ?', [2500])
->get();
orderByRaw
Phương thức orderByRaw có thể được sử dụng để cung cấp một chuỗi thô là giá trị của mệnh đề "order by":
$orders = DB::table('orders')
->orderByRaw('updated_at - created_at DESC')
->get();
groupByRaw
Phương thức groupByRaw có thể được sử dụng để cung cấp một chuỗi thôi là giá trị của mệnh đề group by:
$orders = DB::table('orders')
->select('city', 'state')
->groupByRaw('city, state')
->get();
Join
Mệnh đề inner join
Query builder cũng có thể được sử dụng để thêm các mệnh đề join vào các truy vấn của bạn. Bạn có thể sử dụng phương thức join trong đó đối số đầu tiên được truyền cho phương thức join là tên của bảng mà bạn cần join, trong khi các đối số còn lại chỉ định các ràng buộc cột join. Bạn thậm chí có thể kết hợp nhiều bảng trong một truy vấn:
use Illuminate\Support\Facades\DB;
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();
Mệnh đề left join/right join
Nếu bạn muốn thực hiện "left join" hoặc "right join" thì bạn hãy sử dụng các phương thức leftJoin hoặc rightJoin. Các phương thức này có cùng cú pháp với phương thức join:
$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
$users = DB::table('users')
->rightJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
Mệnh đề cross join
Bạn có thể sử dụng phương thức crossJoin để thực hiện "cross join":
$sizes = DB::table('sizes')
->crossJoin('colors')
->get();
Các mệnh đề join nâng cao
Bạn cũng có thể chỉ định các mệnh đề join nâng cao hơn. Để bắt đầu ta sẽ truyền một closure làm đối số thứ hai cho phương thức join.
Ví dụ:
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
})
->get();
Nếu bạn muốn sử dụng mệnh đề "where" trên các phép nối của mình, bạn có thể sử dụng các phương thức where và orWhere:
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')
->where('contacts.user_id', '>', 5);
})
->get();
Kết hợp truy vấn con
Bạn có thể sử dụng các phương thức joinSub, leftJoinSub và rightJoinSub để join một truy vấn với một truy vấn con. Mỗi phương thức này sẽ nhận ba đối số: truy vấn con, bí danh bảng của nó và một closure để định nghĩa các cột liên quan. Ví dụ sau sẽ truy xuất một tập hợp người dùng trong đó mỗi bản ghi người dùng cũng chứa timestamps created_at của bài đăng blog được xuất bản gần đây nhất của người dùng:
$latestPosts = DB::table('posts')
->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
->where('is_published', true)
->groupBy('user_id');
$users = DB::table('users')
->joinSub($latestPosts, 'latest_posts', function ($join) {
$join->on('users.id', '=', 'latest_posts.user_id');
})->get();
Union
Query builder cũng cung cấp một phương thức thuận tiện để "kết hợp" hai hoặc nhiều truy vấn lại với nhau. Ví dụ: bạn có thể tạo một truy vấn ban đầu và sử dụng phương thức union để kết hợp nó với các truy vấn khác:
use Illuminate\Support\Facades\DB;
$first = DB::table('users')
->whereNull('first_name');
$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();
Ngoài phương thức union ra thì ta còn phương thức unionAll. Các truy vấn được kết hợp bằng phương thức unionAll sẽ không bị xóa các kết quả trùng lặp. unionAll có cú pháp giống với union.
Các mệnh đề where cơ bản
Mệnh đề where
Phương thức where cơ bản nhất yêu cầu ba đối số. Đối số đầu tiên là tên của cột. Đối số thứ hai là một toán tử, có thể là bất kỳ toán tử nào được hỗ trợ của cơ sở dữ liệu. Đối số thứ ba là giá trị để so sánh với giá trị của cột.
Ví dụ: truy vấn sau đây truy xuất người dùng trong đó giá trị của cột votes bằng 100 và giá trị của cột age lớn hơn 35:
$users = DB::table('users')
->where('votes', '=', 100)
->where('age', '>', 35)
->get();
Để thuận tiện, nếu bạn muốn rằng một cột là = một giá trị nhất định, bạn có thể chuyển giá trị làm đối số thứ hai như sau:
$users = DB::table('users')->where('votes', 100)->get(); //tương đương với where votes = 100
Như đã nói đến ở trên, bạn có thể sử dụng bất kỳ toán tử nào được hỗ trợ bởi hệ thống cơ sở dữ liệu của bạn:
$users = DB::table('users')
->where('votes', '>=', 100)
->get();
$users = DB::table('users')
->where('votes', '<>', 100)
->get();
$users = DB::table('users')
->where('name', 'like', 'T%')
->get();
Bạn cũng có thể truyền một mảng các điều kiện cho hàm where, trong đó mỗi phần tử của mảng phải là một mảng chứa ba đối số thường được truyền cho phương thức where:
$users = DB::table('users')->where([
['status', '=', '1'],
['subscribed', '<>', '1'],
])->get();
Hàm orWhere
Bạn có thể sử dụng phương thức orWhere để nối một mệnh đề với truy vấn bằng toán tử or. Cú pháp của orWhere tương tự như cú pháp của where:
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();
Nếu bạn cần nhóm một điều kiện "or" trong cặp ngoặc tròn thì bạn có thể truyền một closure làm đối số đầu tiên cho orWhere:
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere(function($query) {
$query->where('name', 'Abigail')
->where('votes', '>', 50);
})
->get();
Ví dụ trên sẽ tạo ra câu lệnh SQL tương ứng sau:
select * from users where votes > 100 or (name = 'Abigail' and votes > 50)
Bạn nên luôn nhóm các lời gọi orWhere để tránh hành vi không mong muốn khi áp dụng các quy tắc chung (global).
Mệnh đề where JSON
Laravel cũng hỗ trợ truy vấn các cột JSON. Để truy vấn một cột JSON ta sử dụng toán tử ->:
$users = DB::table('users')
->where('preferences->dining->meal', 'salad')
->get();
Bạn có thể sử dụng phương thức whereJsonContains để truy vấn mảng JSON. Tính năng này không được cơ sở dữ liệu SQLite hỗ trợ:
$users = DB::table('users')
->whereJsonContains('options->languages', 'en')
->get();
Nếu ứng dụng của bạn sử dụng cơ sở dữ liệu MySQL hoặc PostgreSQL, bạn có thể chuyển một mảng giá trị cho phương thức whereJsonContains như sau:
$users = DB::table('users')
->whereJsonContains('options->languages', ['en', 'de'])
->get();
Bạn có thể sử dụng whereJsonLength để truy vấn mảng JSON theo độ dài của chúng:
$users = DB::table('users')
->whereJsonLength('options->languages', 0)
->get();
$users = DB::table('users')
->whereJsonLength('options->languages', '>', 1)
->get();
Các mệnh where khác
whereBetween / orWhereBetween
Phương thức whereBetween dùng để kiểm tra xem giá trị của cột có nằm giữa hai giá trị không:
$users = DB::table('users')
->whereBetween('votes', [1, 100])
->get();
whereNotBetween / orWhereNotBetween
Phương thức whereNotBetween dùng để kiểm tra xem giá trị của cột có nằm ngoài hai giá trị không:
$users = DB::table('users')
->whereNotBetween('votes', [1, 100])
->get();
whereIn / whereNotIn / orWhereIn / orWhereNotIn
Phương thức whereIn dùng để kiểm tra xem giá trị của cột có là một trong các giá trị trong mảng không:
$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();
Phương thức whereNotIn dùng để kiểm tra xem giá trị của cột có không là một trong các giá trị trong mảng không:
$users = DB::table('users')
->whereNotIn('id', [1, 2, 3])
->get();
Nếu bạn đang thêm một mảng lớn các liên kết số nguyên vào truy vấn của mình, thì các phương thức whereIntegerInRaw hoặc whereIntegerNotInRaw có thể được sử dụng để giảm đáng kể mức sử dụng bộ nhớ của bạn.
whereNull / whereNotNull / orWhereNull / orWhereNotNull
Phương thức whereNull dùng để kiểm tra xem giá trị của cột có NULL không:
$users = DB::table('users')
->whereNull('updated_at')
->get();
Phương thức whereNotNull dùng để kiểm tra xem giá trị của cột có không phải là NULL không:
$users = DB::table('users')
->whereNotNull('updated_at')
->get();
whereDate / whereMonth / whereDay / whereYear / whereTime
Phương thức whereDate có thể được sử dụng để so sánh cột với giá trị năm tháng ngày đưa ra:
$users = DB::table('users')
->whereDate('created_at', '2016-12-31')
->get();
Phương thức whereMonth có thể được sử dụng để so sánh cột với giá trị tháng đưa ra:
$users = DB::table('users')
->whereMonth('created_at', '12')
->get();
Phương thức whereDay có thể được sử dụng để so sánh cột với giá trị ngày đưa ra:
$users = DB::table('users')
->whereDay('created_at', '31')
->get();
Phương thức whereYear có thể được sử dụng để so sánh cột với giá trị năm đưa ra:
$users = DB::table('users')
->whereYear('created_at', '2016')
->get();
Phương thức whereTime có thể được sử dụng để so sánh cột với giá trị giờ phút giây đưa ra:
$users = DB::table('users')
->whereTime('created_at', '=', '11:20:45')
->get();
whereColumn / orWhereColumn
Phương thức whereColumn có thể được sử dụng để so sánh hai cột:
$users = DB::table('users')
->whereColumn('first_name', 'last_name')
->get();
Bạn cũng có thể truyền một toán tử so sánh cho phương thức whereColumn:
$users = DB::table('users')
->whereColumn('updated_at', '>', 'created_at')
->get();
Bạn cũng có thể truyền một mảng so sánh cột cho phương thức whereColumn:
$users = DB::table('users')
->whereColumn([
['first_name', '=', 'last_name'],
['updated_at', '>', 'created_at'],
])->get();
Nhóm logic
Đôi khi bạn có thể cần phải nhóm một số mệnh đề "where" để đạt được nhóm logic mong muốn cho câu lệnh truy vấn. Trên thực tế, bạn nên nhóm các lệnh gọi đến phương thức orWhere trong cặp ngoặc tròn sử dụng closure để tránh hành vi truy vấn không mong muốn. Để thực hiện điều này, bạn có thể truyền một closure cho phương thức where:
$users = DB::table('users')
->where('name', '=', 'John')
->where(function ($query) {
$query->where('votes', '>', 100)
->orWhere('title', '=', 'Admin');
})
->get();
Ví dụ trên sẽ tạo ra câu lệnh SQL tương ứng sau:
select * from users where name = 'John' and (votes > 100 or title = 'Admin')
Bạn nên luôn closure hàm orWhere để tránh hành vi không mong muốn.
Các mệnh đề where nâng cao
Mệnh đề where exists
Phương thức whereExists dùng để kiểm tra xem có tồn tại bản ghi mong muốn không, nó tương đương với mệnh đề "where exists" trong SQL. whereExists chấp nhận một closure:
$users = DB::table('users')
->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereColumn('orders.user_id', 'users.id');
})
->get();
Truy vấn trên sẽ tạo ra câu lệnh SQL tương ứng sau:
select * from users
where exists (
select 1
from orders
where orders.user_id = users.id
)
Mệnh đề where chứa truy vấn con
Đôi khi bạn có thể cần phải xây dựng một mệnh đề "where" để so sánh kết quả của một truy vấn con với một giá trị nhất định. Bạn có thể thực hiện điều này bằng cách truyền một closure và một giá trị cho phương thức where.
Ví dụ: truy vấn sau sẽ truy xuất tất cả người dùng có "tư cách thành viên" gần đây của một kiểu đã cho:
use App\Models\User;
$users = User::where(function ($query) {
$query->select('type')
->from('membership')
->whereColumn('membership.user_id', 'users.id')
->orderByDesc('membership.start_date')
->limit(1);
}, 'Pro')->get();
Hoặc, bạn có thể cần phải xây dựng một mệnh đề "where" để so sánh một cột với kết quả của một truy vấn con. Bạn có thể thực hiện điều này bằng cách chuyển một cột, toán tử và closure cho phương thức where. Ví dụ: truy vấn sau sẽ truy xuất tất cả các bản ghi thu nhập trong đó số tiền nhỏ hơn mức trung bình:
use App\Models\Income;
$incomes = Income::where('amount', '<', function ($query) {
$query->selectRaw('avg(i.amount)')->from('incomes as i');
})->get();
Order By, Group By, Limit và Offset
Order By
Phương thức orderBy
Phương thức orderBy (Order By trong SQL) cho phép bạn sắp xếp các kết quả của truy vấn bởi một cột nhất định. Đối số đầu tiên được phương thức orderBy chấp nhận phải là cột bạn muốn sắp xếp, còn đối số thứ hai xác định hướng sắp xếp và có thể là asc hoặc desc:
$users = DB::table('users')
->orderBy('name', 'desc')
->get();
Để sắp xếp theo nhiều cột bạn chỉ cần gọi orderBy nhiều lần nếu cần:
$users = DB::table('users')
->orderBy('name', 'desc')
->orderBy('email', 'asc')
->get();
Phương thức latest và oldest
Các phương thức latest và oldest cho phép bạn dễ dàng sắp xếp kết quả theo ngày. Theo mặc định, kết quả sẽ được sắp xếp theo cột created_at của bảng. Hoặc, bạn có thể chuyển tên cột mà bạn muốn sắp xếp theo:
$user = DB::table('users')
->latest()
->first();
Order By ngẫu nhiên
Phương thức inRandomOrder có thể được sử dụng để sắp xếp các kết quả truy vấn một cách ngẫu nhiên. Ví dụ: bạn có thể sử dụng phương thức này để lấy một user ngẫu nhiên như sau:
$randomUser = DB::table('users')
->inRandomOrder()
->first();
Loại bỏ các mệnh đề Order By hiện có
Phương thức reorder sẽ loại bỏ tất cả các mệnh đề "order by" mà trước đây đã được áp dụng cho các truy vấn:
$query = DB::table('users')->orderBy('name');
$unorderedUsers = $query->reorder()->get();
Bạn có thể truyền một cột và hướng (asc hay desc) khi gọi phương thức reorder để loại bỏ tất cả các mệnh đề "order by" hiện có và áp dụng việc sắp xếp hoàn toàn mới cho truy vấn:
$query = DB::table('users')->orderBy('name');
$usersOrderedByEmail = $query->reorder('email', 'desc')->get();
Group By
Phương thức groupBy và having
Các phương thức groupBy và having (Group By và Having trong SQL) có thể được sử dụng để nhóm các kết quả truy vấn:
$users = DB::table('users')
->groupBy('account_id')
->having('account_id', '>', 100)
->get();
Bạn có thể truyền nhiều đối số cho groupBy để nhóm theo nhiều cột:
$users = DB::table('users')
->groupBy('first_name', 'status')
->having('account_id', '>', 100)
->get();
Để xây dựng các cậu lệnh truy vấn với having nâng cao hơn bạn có thể xem phương thức havingRaw.
Limit và Offset
Các phương thức skip và take
Bạn có thể sử dụng các phương thức skip và take để giới hạn số lượng kết quả trả về từ truy vấn hoặc bỏ qua một số kết quả nhất định trong truy vấn:
$users = DB::table('users')->skip(10)->take(5)->get();
Ngoài ra, bạn có thể sử dụng các phương thức limit và offset. Các phương thức này tương đương về mặt chức năng với các phương thức take và skip:
$users = DB::table('users')
->offset(10)
->limit(5)
->get();
Phương thức when
Đôi khi bạn có thể muốn các mệnh đề truy vấn nhất định áp dụng cho một truy vấn dựa trên một điều kiện khác. Ví dụ: bạn có thể chỉ muốn áp dụng một câu lệnh where nếu một giá trị đầu vào nhất định có trong yêu cầu đến HTTP. Bạn có thể thực hiện điều này bằng cách sử dụng phương thức when:
$role = $request->input('role');
$users = DB::table('users')
->when($role, function ($query, $role) {
return $query->where('role_id', $role);
})
->get();
Phương thức when chỉ thực hiện closure khi đối số đầu tiên là true. Nếu đối số đầu tiên là false, thì closure sẽ không được thực hiện. Vì vậy, trong ví dụ trên, closure được cung cấp cho phương thức when sẽ chỉ được gọi nếu trường role có trong yêu cầu đến và có giá trị là true.
Bạn có thể truyền một closuere khác làm đối số thứ ba cho phương thức when. Closure này sẽ chỉ thực thi nếu đối số đầu tiên mang giá trị là false. Để minh họa cách sử dụng tính năng này, ta sẽ sử dụng nó để định cấu hình thứ tự mặc định của một truy vấn:
$sortByVotes = $request->input('sort_by_votes');
$users = DB::table('users')
->when($sortByVotes, function ($query, $sortByVotes) {
return $query->orderBy('votes');
}, function ($query) {
return $query->orderBy('name');
})
->get();
Phương thức insert
Query Builder cũng cung cấp phương thức insert có thể được sử dụng để chèn các bản ghi vào bảng cơ sở dữ liệu. Phương thức insert chấp nhận một loạt các tên cột và các giá trị:
DB::table('users')->insert([
'email' => 'kayla@example.com',
'votes' => 0
]);
Ta có thể chèn nhiều bản ghi cùng một lúc bằng cách truyền một mảng các mảng. Mỗi mảng đại diện cho một bản ghi cần được chèn vào bảng:
DB::table('users')->insert([
['email' => 'picard@example.com', 'votes' => 0],
['email' => 'janeway@example.com', 'votes' => 0],
]);
Phương thức insertOrIgnore sẽ bỏ qua lỗi bản ghi trùng lặp trong khi chèn các bản ghi vào cơ sở dữ liệu:
DB::table('users')->insertOrIgnore([
['id' => 1, 'email' => 'sisko@example.com'],
['id' => 2, 'email' => 'archer@example.com'],
]);
ID tự động tăng
Nếu bảng có id tăng tự động thì ta sử dụng phương thức insertGetId để chèn bản ghi và sau đó truy xuất ID:
$id = DB::table('users')->insertGetId(
['email' => 'john@example.com', 'votes' => 0]
);
Khi sử dụng PostgreSQL, phương thức insertGetId sẽ đặt tên cho cột tự động tăng dần id. Nếu bạn muốn truy xuất ID từ một "chuỗi" khác, bạn có thể truyền tên cột làm tham số thứ hai cho phương thức insertGetId.
Phương thức upsert
Phương thức upsert sẽ chèn bản ghi mà không tồn tại và cập nhật bản ghi đã tồn tại với các giá trị mới mà bạn có thể chỉ định. Đối số đầu tiên của phương thức bao gồm các giá trị để chèn hoặc cập nhật, trong khi đối số thứ hai liệt kê (các) cột xác định duy nhất các bản ghi trong bảng được liên kết. Đối số thứ ba và là cuối cùng của phương thức là một mảng các cột cần được cập nhật nếu bản ghi phù hợp đã tồn tại trong cơ sở dữ liệu:
DB::table('flights')->upsert([
['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
], ['departure', 'destination'], ['price']);
Trong ví dụ trên, Laravel sẽ cố gắng chèn hai bản ghi. Nếu một bản ghi đã tồn tại với các giá trị cột tương tự nhau là departure và destination, thì Laravel sẽ cập nhật cột price của bản ghi đó.
Tất cả các cơ sở dữ liệu ngoại trừ SQL Server yêu cầu các cột trong đối số thứ hai của phương thức upsert phải có chỉ mục "khóa chính - PK" hoặc "duy nhất - Unique".
Phương thức update
Ngoài việc chèn các bản ghi vào cơ sở dữ liệu, trình tạo truy vấn cũng có thể cập nhật các bản ghi hiện có bằng phương thức update. Phương thức update giống như phương thức insert, nó chấp nhận một loạt các cặp cột và giá trị chỉ ra các cột được cập nhật. Bạn có thể ràng buộc phương thức update với phương thức where:
$affected = DB::table('users')
->where('id', 1)
->update(['votes' => 1]);
Phương thức updateOrInsert
Đôi khi bạn có thể muốn cập nhật một bản ghi hiện có trong cơ sở dữ liệu hoặc tạo nó nếu không có bản ghi phù hợp nào tồn tại. Trong trường hợp này ta có thể sử dụng phương thức updateOrInsert. updateOrInsert chấp nhận hai đối số: một mảng các điều kiện để tìm ra bản ghi, và một mảng của các cặp cột và giá trị chỉ ra các cột được cập nhật.
Phương thức updateOrInsert sẽ cố gắng xác định vị trí một bản ghi trong cơ sở dữ liệu phù hợp sử dụng cặp cột và giá trị của đối số đầu tiên. Nếu bản ghi tồn tại thì nó sẽ được cập nhật với các giá trị trong đối số thứ hai. Nếu không thể tìm thấy bản ghi thì bản ghi mới sẽ được chèn với các thuộc tính đã hợp nhất của cả hai đối số:
DB::table('users')
->updateOrInsert(
['email' => 'john@example.com', 'name' => 'John'],
['votes' => '2']
);
Cập nhật các cột JSON
Khi cập nhật một cột JSON, bạn nên sử dụng cú pháp -> để cập nhật khóa thích hợp trong đối tượng JSON. Thao tác này được hỗ trợ trên MySQL 5.7+ và PostgreSQL 9.5+:
$affected = DB::table('users')
->where('id', 1)
->update(['options->enabled' => true]);
Phương thức increment() và decrement()
Trình tạo truy vấn cũng cung cấp các phương thức thuận tiện để tăng hoặc giảm giá trị của một cột nhất định. Cả hai phương thức này đều chấp nhận ít nhất một đối số: cột cần sửa đổi. Đối số thứ hai có thể được cung cấp để chỉ định số lượng cột sẽ được tăng hoặc giảm:
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);
Bạn cũng có thể chỉ định các cột bổ sung để cập nhật trong quá trình hoạt động:
DB::table('users')->increment('votes', 1, ['name' => 'John']);
Xóa dữ liệu bảng
Phương thức delete của trình tạo truy vấn có thể được sử dụng để xóa các bản ghi khỏi bảng. Bạn có thể ràng buộc phương thức delete bằng cách đặt phương thức where trước phương thức delete:
DB::table('users')->delete();
DB::table('users')->where('votes', '>', 100)->delete();
Nếu bạn muốn xóa dữ liệu trên toàn bộ bảng thì ta dùng phương thức truncate, phương thức này sẽ xóa tất cả các bản ghi khỏi bảng và đặt lại ID tự động tăng dần về 0:
DB::table('users')->truncate();
Xóa dữ liệu (truncate) bảng trong PostgreSQL
Khi xóa dữ liệu trong PostgreSQL thì hành vi CASCADE sẽ được áp dụng, điều này có nghĩa là tất cả các bản ghi liên quan đến khóa ngoại trong các bảng khác cũng sẽ bị xóa.
Khóa bi quan (Pessimistic Locking)
Trình tạo truy vấn cũng có một vài chức năng để giúp bạn có được "khóa bi quan" khi thực hiện các câu lệnh select. Để thực hiện một câu lệnh với "khóa chia sẻ - shared lock", bạn có thể gọi phương thức sharedLock. Khóa chia sẻ sẽ ngăn các hàng đã chọn không được sửa đổi cho đến khi giao dịch của ta hoàn thành (committed):
DB::table('users')
->where('votes', '>', 100)
->sharedLock()
->get();
Ngoài ra, bạn có thể sử dụng phương thức lockForUpdate. Khóa "để cập nhật - for update" ngăn không cho sửa đổi các bản ghi đã chọn hoặc không được chọn bằng một khóa chia sẻ khác:
DB::table('users')
->where('votes', '>', 100)
->lockForUpdate()
->get();
Gỡ lỗi
Bạn có thể sử dụng các phương thức dd và dump khi xây dựng một truy vấn để kết xuất các ràng buộc truy vấn hiện tại và SQL. Phương thức dd sẽ hiển thị các thông tin gỡ lỗi và sau đó dừng thực hiện các yêu cầu. Phương thức dump sẽ hiển thị các thông tin gỡ lỗi nhưng cho phép các yêu cầu tiếp tục thực hiện:
DB::table('users')->where('votes', '>', 100)->dd();
DB::table('users')->where('votes', '>', 100)->dump();