[Bài đọc] Query Builder

Cơ sở dữ liệu

Giới thiệu

Query builder cung cấp giao diện linh hoạt để tạo và thực thi các truy vấn dữ liệu. Query builder có thể được sử dụng thể thực hiện hầu hết các thao tác dữ liệu trong ứng dụng của bạn và làm việc trên các hệ cơ sở dữ liệu được hỗ trợ.

Laravel query builder sử dụng PDO parameter binding để bảo vệ ứng dụng của bạn khỏi SQL injection. Vì vậy không cần phải xử lý các chuỗi khi truyền vào.

Lấy tập kết quả trả về

Lấy toàn bộ số dòng trong một bảng

Bạn có thể sử dụng phương thức table trên façade DB để bắt đầu một truy vấn. Phương thức table trả về một thể hiện fluent query builder với bảng đã cho, cho phép bạn thêm nhiều ràng buộc vào truy vấn và cuối cùng là lấy kết quả trả về bằng phương thức get:

<?php
namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
    /**
     * Show a list of all of the application's users.
     *
     * @return Response
     */
    public function index()
    {
        $users = DB::table('users')->get(); 
        return view('user.index', ['users' => $users]);
    }
}

Phương thức get trả về một Illuminate\Support\Collection chứa tập kết quả mà mỗi kết quả là một thể hiện của đối tượng StdClass trong PHP. Bạn có thể truy cập vào mỗi cột giá trị bằng cách coi cột như là thuộc tính của đối tượng:

foreach ($users as $user) {
    echo $user->name;
}

Lấy về một dòng/cột từ bảng

Nếu bạn chỉ cần lấy một dòng từ bảng 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();
echo $user->name;

Nếu bạn không cần lấy toàn bộ dòng, bạn có thể lấy ra một giá trị từ một bản ghi sử dụng phương thức value. Phương thức này trả về giá trị của cột:

$email = DB::table('users')->where('name', 'John')->value('email');

Retrieving A List Of Column Values

Nếu bạn muốn lấy về một Collection chứa các giá trị của một cột, bạn có thể sử dụng phương thức pluck. Trong ví dụ này, chúng ta sẽ lấy về một Collection gồm các title của bảng roles

$titles = DB::table('roles')->pluck('title');
foreach ($titles as $title) {
    echo $title;
}

Bạn cũng có thể chỉ định một cột là khóa cho tập kết quả trả về:

$roles = DB::table('roles')->pluck('title', 'name');
foreach ($roles as $name => $title) {
    echo $title;
}

Chia nhỏ tập kết quả (Chunking Results)

Nếu bạn phải làm việc với hàng nghìn bản ghi, hãy xem xét việc sử dụng phương thức chunk. Phương thức này trả về một chunk các kết quả tại một thời điểm, và đưa mỗi chuck (phần) này vào một Closure để xử lý. Phương thức này vô cùng hữu ích cho việc viết các lệnh artisan để xử lý hàng nghìn bản ghi. Ví dụ, hãy làm việc với toàn bộ bảng users lấy về các chunks chứa 100 bản ghi cùng một lúc:

DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    foreach ($users as $user) {
        //
    }
});

Bạn có thể dừng các chuck khác khỏi việc xử lý bằng cách trả về false từ Closure:

DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    // Process the records...
    return false;
});

Hàm tập hợp

Query builder cũng cung cấp một số hàm tập hợp (aggregate method) như count, max, min, avg và sum. Bạn có thể gọi bất kỳ phương thức nào sau câu trúc truy vấn:

$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');

Tất nhiên, bạn có thể gộp các phương thức này với các mệnh đề khác:

$price = DB::table('orders')
                ->where('finalized', 1)
                ->avg('price');

Selects

Xác định câu lệnh select

Tất nhiên, không phải lúc nào bạn cũng muốn lấy về tất cả các cột trong bảng. Sử dụng phương thức select, bạn có thể chỉ định tùy chọn mềnh đề select cho truy vấn:

$users = DB::table('users')->select('name', 'email as user_email')->get();

Phương thức distinct cho phép truy vấn trả về tập kết quả phân biệt:

$users = DB::table('users')->distinct()->get();

Nếu bạn đã có sẵn một query builder và bạn muốn thêm vào một cột trong lệnh select bạn có thể sử dụng phương thức addSelect:

$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();

Viết các biểu thức thuần SQL

Đôi khi bạn cần sử dụng một biểu thức thuần SQL trong truy vấn. Những biểu thức này sẽ được đưa vào truy vấn như các chuỗi, vì vậy hãy cẩn thận để không gây lỗi nào. Để tạo một raw expression bạn có thể sử dụng phương thức DB::raw:

$users = DB::table('users')
                   ->select(DB::raw('count(*) as user_count, status'))
                   ->where('status', '<>', 1)
                   ->groupBy('status')
                   ->get();

Câu lệnh JOIN

Mệnh đề Inner Join

Query builder cũng có thể được sử dụng để viết các lệnh join. Để thực hiện một inner join đơn giản, bạn có thể sử dụng phương thức join cho một query builder instance. Tham số được truyền cho phương thức join là tên của bảng bạn cần join đến, tham số còn lại chỉ định các cột ràng buộc khi kết nối giữa các bảng. Tất nhiên, như bạn thấy, bạn có thể kết nối nhiều bảng trong một truy vấn:

$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

Nếu bạn muốn thực hiện một kết nối left join thay vì inner join, sử dụng phương thức leftJoin. Phương thức này có cú pháp giống với phương thức join

$users = DB::table('users')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

Mệnh đề Cross Join

Để thực hiện một cross join, sử dụng phương thức crossJoin với tên bảng bạn muốn cross join đến. Cross join sinh ra một cartesian product (tích descartes là một tập hợp A x B của A và B) giữa bảng đầu tiên và bảng được kết nối:

$users = DB::table('sizes')
            ->crossJoin('colours')
            ->get();

Mệnh đề Join nâng cao

Bạn cũng có thể chỉ định nhiều mệnh đề join nâng cao. Để bắt đầu, truyền Closure làm tham số thứ hai trong phương thức join. Closure sẽ nhận một đối tượng JoinClause mà cho phép bạn chỉ định các ràng buộc trong mệnh đề join:

DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
        })
        ->get();

Nếu bạn mốn sử dụng mệnh đề where với join, bạn có thể sử dụng phương thức where và orWhere. Thay vì so sánh hai cột các phương thức nãy sẽ so sánh cột với giá trị:

DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')
                 ->where('contacts.user_id', '>', 5);
        })
        ->get();

Nối tập các kết quả

Query builder cũng cung cấp cách để nối dữ liệu từ hai truy với nhau. Ví dụ bạn tạo một truy vấn khởi tạo, và sau đó sử dụng phương thức union để nối nó vào truy vấn thứ hai:

$first = DB::table('users')
            ->whereNull('first_name');

$users = DB::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

Lưu ý: Phương thức unionAll cũng có cách sử dụng như union

Mệnh đề where

Mệnh đề where đơn giản

Để thêm mệnh đề where vào truy vấn sử dụng phương thức where trong query builder. Phương thức where gồm ba tham số cơ bản, tham số đầu tiên là tên của cột, tham số thứ hai là một toán tử, tham số thứ ba là giá trị để so sánh với cột.

Ví dụ dưới đây là một truy vấn mà kiểm tra giá trị của cột votes có bằng 100 hay không:

$users = DB::table('users')->where('votes', '=', 100)->get();

Để thuận tiện, nếu bạn muốn lấy một cột có giá trị bằng giá trị đã cho, bạn chỉ cần truyền giá trị trực tiếp vào như là tham số thứ hai cho phương thức where:

$users = DB::table('users')->where('votes', 100)->get();

Tất nhiên, bạn có thể sử dụng nhiều toán tử khác với mệnh đề where:

$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ó thể truyền một mảng điều kiện vào phương thức where:

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

Lệnh Or

Bạn có thể nối các ràng buộc where với nhau giống như thêm mệnh đề or vào truy vấn. Phương thức orWhere nhận các tham số giống như phương thức where:

$users = DB::table('users')
                    ->where('votes', '>', 100)
                    ->orWhere('name', 'John')
                    ->get();

Các mệnh đề where khác

whereBetween

Phương thức whereBetween kiểm tra giá trị một cột có ở giữa hai giá trị hay không:

$users = DB::table('users')
                    ->whereBetween('votes', [1, 100])->get();

whereNotBetween

Phương thức whereNotBetween kiểm tra giá trị của cột có nằm bên ngoài hai giá trị hay không:

$users = DB::table('users')
                    ->whereNotBetween('votes', [1, 100])
                    ->get();

whereIn / whereNotIn

Phương thức whereIn kiểm tra giá trị của cột đã cho có thuộc về một mảng cho trước hay không:

$users = DB::table('users')
                    ->whereIn('id', [1, 2, 3])
                    ->get();

Phương thức whereNotIn kiểm tra giá trị của cột đã cho không nằm trong mảng cho trước hay không:

$users = DB::table('users')
                    ->whereNotIn('id', [1, 2, 3])
                    ->get();

whereNull / whereNotNull

Phương thức whereNull kiểm tra giá trị của cột đã cho có là NULL hay không:

$users = DB::table('users')
                    ->whereNull('updated_at')
                    ->get();

Phương thức whereNotNull kiểm tra giá trị của cột đã cho có là not null hay không::

$users = DB::table('users')
                    ->whereNotNull('updated_at')
                    ->get();

whereDate / whereMonth / whereDay / whereYear

Phương thức whereDate sử dụng để so sánh giá trị cột đã cho với kiểu ngày tháng năm::

$users = DB::table('users')
                ->whereDate('created_at', '2016-12-31')
                ->get();

Phương thức whereMonth sử dụng để so sánh giá trị cột đã cho với giá trị của tháng trong năm:

$users = DB::table('users')
                ->whereMonth('created_at', '12')
                ->get();

Phương thức whereDay sử dụng để so sánh giá trị cột đã cho với giá trị của ngày trong tháng:

$users = DB::table('users')
                ->whereDay('created_at', '31')
                ->get();

Phương thức whereYear sử dụng để so sánh giá trị cột đã cho với giá trị của năm:

$users = DB::table('users')
                ->whereYear('created_at', '2016')
                ->get();

whereColumn

Phương thức whereColumn kiểm tra hai giá trị của cột có bằng nhau hay không:

$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 vào phương thức whereColumn:

$users = DB::table('users')
                ->whereColumn('updated_at', '>', 'created_at')
                ->get();

Phương thức whereColumn có thể được truyền vào một mảng điều kiện. Những điều kiện này được nối với nhau sử dụng toán tử and:

$users = DB::table('users')
                ->whereColumn([
                    ['first_name', '=', 'last_name'],
                    ['updated_at', '>', 'created_at']
                ])->get();

Nhóm tham số (Parameter Grouping)

Thỉnh thoảng bạn cần tạo nhiều mệnh đề where nâng cao như mệnh đề where exists hoặc nhóm các tham số lồng nhau. Laravel query builder có thể xử lý việc này khá tốt. Để bắt đầu, hãy xem ví dụ sau về nhóm các ràng buộc trong ngoặc:

DB::table('users')
            ->where('name', '=', 'John')
            ->orWhere(function ($query) {
                $query->where('votes', '>', 100)
                      ->where('title', '<>', 'Admin');
            })
            ->get();

Như bạn có thể thấy, truyền một Closure vào trong phương thức orWhere. Closure sẽ nhận một query builder mà bạn có thể sử dụng để thiết lập các ràng buộc được đặt trong ngoặc. Ví dụ trên sẽ tạo ra một câu lệnh sql như sau:

select * from users where name = 'John' or (votes > 100 and title <> 'Admin')

Mệnh đề Where Exists

Phương thức whereExists cho phép bạn viết các lệnh sql với where exitsts. Phương thức này nhận một Closure làm tham số, mà sẽ chấp nhận một query builder bạn tạo trong truy vấn đặt trong mệnh đề exists:

DB::table('users')
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                      ->from('orders')
                      ->whereRaw('orders.user_id = users.id');
            })
            ->get();

Truy vấn trên sẽ sinh ra lệnh sql như sau:

select * from users
where exists (
    select 1 from orders where orders.user_id = users.id
)

Mệnh đề JSON Where

Laravel hỗ trợ truy vấn với cột có kiểu dữ liệu JSON. Hiện tại các hỗ trợ này có trong MySQL 5.7 và Postgres. Để truy vấn cột JSON, sử dụng toán tử ->:

$users = DB::table('users')
                ->where('options->language', 'en')
                ->get();

$users = DB::table('users')
                ->where('preferences->dining->meal', 'salad')
                ->get();

Sắp xếp, nhóm, giới hạn kết quả truy vấn

orderBy

Phương thức orderBy cho phép bạn sắp xếp tập kết quả trả về của truy vấn theo một cột cho trước. Tham số đầu tiên của phương thức orderBy phải là tên cột mà bạn muốn sắp xếp, trong khi đó tham số thứ hai là chiều sắp xếp theo tăng dần (asc) hoặc giảm dần (desc):

$users = DB::table('users')
                ->orderBy('name', 'desc')
                ->get();

latest / oldest

Phương thức latest và oldest cho phép bạn sắp xếp kết quả trả về theo ngày tháng năm. Mặc định, tập kết quả trả về được sắp xếp theo cột created_at. Ngoài ra bạn có thể truyền tên cột muốn sắp xếp vào:

$user = DB::table('users')
                ->latest()
                ->first();

inRandomOrder

Phương thức inRandomOrder được sử dụng để sắp xếp tập kết quả trả về một cách ngẫn 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:

$randomUser = DB::table('users')
                ->inRandomOrder()
                ->first();

groupBy / having / havingRaw

Phương thức groupBy và having có thể được sử dụng để nhóm tập kết quả trả về. Phương thức having sử dụng giống phương thức where, để giới hạn kết quả trả về:

$users = DB::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();

Phương thức havingRaw sử dụng để thiết lập các chuỗi vào mệnh đề having. Ví dụ, chúng ta có thể tìm tất cả các departement mà có sales lớn hơn $2,500:

$users = DB::table('orders')
                ->select('department', DB::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > 2500')
                ->get();

skip / take

Để giới hạn số kết quả trả về từ truy vấn, hoặc bỏ qua một số cho trước các kết quả trả về trong truy vấn, bạn có thể sử dụng phương thức skip và take:

$users = DB::table('users')->skip(10)->take(5)->get();

Ngoài ra, bạn có thể sử dụng phương thức limit và offset:

$users = DB::table('users')
                ->offset(10)
                ->limit(5)
                ->get();

Mệnh đề điều kiện

Thỉnh thoảng bạn muốn một mệnh đề được gắn vào một truy vấn chỉ khi một cái gì đó đúng. Ví dụ, bạn chỉ muốn áp dụng lệnh where nếu giá trị đầu vào được xuất hiện trong request đến. 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) use ($role) {
                    return $query->where('role_id', $role);
                })
                ->get();

Phương thức when chỉ thực hiện Closure khi tham số đầu tiên là đúng. Nếu tham số đầu tiên là sai, Closure sẽ không được thực thi.

Bạn có thể truyền một Closure khác như là một tham số thứ ba cho phương thức when. Closure này sẽ thực thi nếu tham số đầu tiên là sai. Để minh họa cách sử dụng tính năng này, chúng ta sẽ sử dụng nó để cấu hình mặc định sắp xếp một truy vấn:

$sortBy = null;

$users = DB::table('users')
                ->when($sortBy, function ($query) use ($sortBy) {
                    return $query->orderBy($sortBy);
                }, function ($query) {
                    return $query->orderBy('name');
                })
                ->get();

Inserts

Query builder cung cấp phương thức insert để chèn một bản ghi vào bảng. Phương thức này nhận một mảng tên các cột và giá trị các cột:

DB::table('users')->insert(
    ['email' => '[email protected]', 'votes' => 0]
);

Bạn có thể chèn nhiều bản ghi vào bảng với một lần gọi phương thức insert bằng cách truyền vào một mảng các mảng giá trị. Mỗi mảng con đại diện cho một dòng được chèn vào bảng:

DB::table('users')->insert([
    ['email' => '[email protected]', 'votes' => 0],
    ['email' => '[email protected]', 'votes' => 0]
]);

Auto-Incrementing IDs

Nếu bảng có một id được tự động tăng, sử dụng phương thức insertGetId để chèn vào một bản ghi vào bảng và sau đó lấy về ID đó:

$id = DB::table('users')->insertGetId(
    ['email' => '[email protected]', 'votes' => 0]
);

Lưu ý: Khi sử dụng với PostgreSQL phương thức insertGetId coi như cột auto-incrementing được đặt tên là id. Nếu bạn muốn lấy giá trị ID từ một sequence khác, bạn có thể truyền tên của sequence như là tham số thức hai trong phương thức insertGetId.

Updates

Tất nhiên, ngoài việc chèn thêm bản ghi vào cơ sở dữ liệu, query builder cũng có thể cập nhật bản ghi bằng cách sử dụng phương thức update. Phương thức update giống như insert, nhận vào một mảng các cặt cột và giá trị để cập nhật. Bạn có thể giới hạn dữ liệu cập nhật với mệnh đề where trong truy vấn:

DB::table('users')
            ->where('id', 1)
            ->update(['votes' => 1]);

Cập nhật cột giá trị JSON

Khi cập nhật cột giá trị JSON, bạn sử dụng toán tử -> để truy cập đến key của đối tượng JSON. Toán tử này chỉ được hỗ trợ trên cơ sở dữ liệu có hỗ trợ kiểu JSON:

DB::table('users')
            ->where('id', 1)
            ->update(['options->enabled' => true]);

hương thức Increment và Decrement

Query builder cung cấp các phương thức để tăng hay giảm giá trị của một cột. Đây là cách ngắn hơn việc sử dụng câu lệnh update.

Cả hai phương thức này đều nhận vào ít nhất một tham số là tên của cột cần cập nhật. Tham số thứ hai là một tùy chọn được truyền vào để điều khiển giá trị tăng hay giảm cho cột:

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ể thêm các cột cần cập nhật trong phương thức này:

DB::table('users')->increment('votes', 1, ['name' => 'John']);

Deletes

Query builder có thể được sử dụng để xóa các bản ghi từ bảng thông qua phương thức delete. Bạn có thể giới hạn dữ liệu bị xóa bằng cách thêm vào mệnh đề where trước khi gọi phương thức delete:

DB::table('users')->delete();

DB::table('users')->where('votes', '>', 100)->delete();

Nếu bạn muốn trancate toàn bộ bảng, tức xóa tất cả các dòng và reset lại giá trị cột tự tăng về 0, bạn có thể sử dụng phương thức truncate:

DB::table('users')->truncate();

Pessimistic Locking

Query builder cũng bao gồm các hàm để giúp bạn “pessimistic locking” trên lệnh select. Để thực thi lệnh với shared lock, bạn có thể sử dụng phương thức sharedLock với truy vấn. Share lock bảo vệ các dòng được lựa chọn khỏi việc thay đổi tới khi một giao dịch của bạn được hoàn tất:

DB::table('users')->where('votes', '>', 100)->sharedLock()->get();

Ngoài ra, bạn có thể sử dụng phương thức lockForUpdate. Một for update lock bảo vệ các dòng khỏi việc thay đổi hoặc bị lựa chọn bởi các share lock khác:

DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();

Trả lời

Email của bạn sẽ không được hiển thị công khai.