Query Builder trong Laravel 13

Ở bài trước chúng ta đã đổ dữ liệu mẫu vào database bằng DB::table()->insert(). Tiếp tục với series, bài này mình sẽ giới thiệu với mọi người về Query Builder trong Laravel 13, rồi làm trang thống kê task theo trạng thái cho TaskFlow.

1. Query Builder là gì?

Query Builder cho phép viết câu truy vấn SQL bằng cách gọi nối tiếp các method PHP, chạy được trên mọi database Laravel hỗ trợ.

Mọi câu query bắt đầu từ DB::table('tên bảng'), với DB là facade Illuminate\Support\Facades\DB (facade sẽ nói ở Bài 25). Giá trị truyền vào được gửi qua PDO parameter binding nên các bạn không cần tự làm sạch chuỗi để chống SQL injection.

2. Lấy dữ liệu.

Các ví dụ mình chạy trong php artisan tinker với dữ liệu mẫu của Bài 13 (3 project, 10 task).

$tasks = DB::table('tasks')->get();
count($tasks); // 10
$tasks[0]->title; // Viết bài Seeder và Factory

DB::table('projects')->where('name', 'TaskFlow API')->first()->id; // 2
DB::table('projects')->find(3)->name; // Du lịch Đà Lạt
DB::table('projects')->where('id', 1)->value('name'); // Website toidicode.com
DB::table('projects')->pluck('name', 'id');
// [1 => 'Website toidicode.com', 2 => 'TaskFlow API', 3 => 'Du lịch Đà Lạt']

Trong đó:

get() trả về một collection (Bài 16) các stdClass, đọc cột bằng $task->title.
first() lấy bản ghi đầu tiên, find($id) lấy theo id, không có thì trả về null. value() lấy một cột.
pluck('cột', 'key') lấy giá trị một cột, tham số thứ hai (không bắt buộc) là cột làm key.

3. Điều kiện, sắp xếp và giới hạn.

Các bạn sử dụng where($column, $operator, $value), trong đó $operator là toán tử SQL (=, !=, >, like...), bỏ đi thì mặc định là =. Nhiều where() liên tiếp nối bằng AND, orWhere() nối bằng OR.

VD: Lấy id các task theo nhiều điều kiện (pluck() trả về collection, mình ghi gọn dạng mảng).

DB::table('tasks')->where('project_id', 1)->where('status', '!=', 'done')->pluck('id'); // [1, 3, 4]
DB::table('tasks')->where('status', 'todo')->orWhere('assignee_id', 3)->pluck('id'); // [3, 4, 7, 8, 9, 10]
DB::table('tasks')->whereIn('status', ['doing', 'done'])->pluck('id'); // [1, 2, 5, 6, 8, 9]
DB::table('tasks')->whereNull('assignee_id')->pluck('id'); // [3, 4, 7, 10]
// hạn trong 7 ngày tới
DB::table('tasks')->whereBetween('due_date', [today(), today()->addDays(7)])->pluck('id'); // [1, 3, 6, 9]
// hoàn thành hôm nay, chỉ so sánh phần ngày
DB::table('tasks')->whereDate('completed_at', today())->pluck('id'); // [2, 5, 8]

Ngược lại có whereNotIn(), whereNotNull(). Để chọn cột các bạn dùng select('id', 'title'), sắp xếp và giới hạn dùng orderBy(), limit(), offset():

DB::table('tasks')
    ->whereNotNull('due_date')
    ->orderBy('due_date') // thêm 'desc' để sắp xếp giảm dần
    ->offset(3)           // bỏ qua 3 bản ghi đầu
    ->limit(3)
    ->pluck('title');     // ['Thiết kế trang chủ', 'Viết tài liệu cho API']

latest() là cách viết gọn của orderBy('created_at', 'desc'), phân trang có ở Bài 16.

Chú ý: Binding chỉ áp dụng cho giá trị, không cho tên cột. Đừng đưa input của người dùng thẳng vào tên cột, VD orderBy($request->query('sort')).

4. Thêm, sửa, xóa và transaction.

insert() các bạn đã gặp ở Bài 13, cần lấy id vừa tạo thì dùng insertGetId(). update() và delete() trả về số bản ghi bị ảnh hưởng:

$id = DB::table('projects')->insertGetId([
    'name' => 'Học Query Builder',
    'created_at' => now(),
    'updated_at' => now(),
]); // 4

// giao các task todo chưa có người làm cho user 1
DB::table('tasks')
    ->where('status', 'todo')
    ->whereNull('assignee_id')
    ->update(['assignee_id' => 1, 'updated_at' => now()]); // 4

DB::table('projects')->where('id', $id)->delete(); // 1

Với cột kiểu số còn có increment('cột', $n), decrement('cột', $n), VD increment('views') tăng 1.

Chú ý: update(), delete() không có where() sẽ chạy trên toàn bộ bảng.

Transaction.

Các câu lệnh phải cùng thành công hoặc cùng thất bại thì bọc trong DB::transaction(): closure chạy xong thì commit, có exception thì rollback rồi ném lại exception.

VD: Tạo project cùng task đầu tiên.

DB::transaction(function () {
    $projectId = DB::table('projects')->insertGetId([
        'name' => 'Học Laravel 13',
        'created_at' => now(),
        'updated_at' => now(),
    ]);

    DB::table('tasks')->insert(['project_id' => $projectId, 'title' => 'Đọc tài liệu']);
});

Nếu insert task lỗi (VD project_id không tồn tại), Laravel ném QueryException và project vừa tạo cũng bị rollback.

Các lệnh trên đã sửa dữ liệu mẫu, chạy lại php artisan migrate:fresh --seed để dữ liệu về như Bài 13 trước khi làm tiếp.

5. Join, groupBy và thống kê task.

join() tạo INNER JOIN, leftJoin() tạo LEFT JOIN. Khi join, các bạn ghi tên bảng trước tên cột, dùng as để đổi tên cột.

VD: Lấy task của project 1 kèm tên project và người được giao.

DB::table('tasks')
    ->join('projects', 'tasks.project_id', '=', 'projects.id')
    ->leftJoin('users', 'tasks.assignee_id', '=', 'users.id')
    ->select('tasks.title', 'projects.name as project', 'users.name as assignee')
    ->where('tasks.project_id', 1)
    ->get();
// Viết bài Seeder và Factory | Website toidicode.com | Nguyễn Văn An
// Viết bài Blade template    | Website toidicode.com | Nguyễn Văn An
// Thiết kế trang chủ         | Website toidicode.com | null
// Tối ưu ảnh trên trang      | Website toidicode.com | null

Hàm tổng hợp có count(), max(), min(), avg(), sum(). Đếm theo nhóm thì dùng groupBy() với DB::raw(), một raw expression được đưa nguyên văn vào câu SQL:

DB::table('tasks')
    ->select('status', DB::raw('count(*) as total'))
    ->groupBy('status')
    ->pluck('total', 'status'); // ['doing' => 3, 'done' => 3, 'todo' => 4]

selectRaw(), whereRaw(), orderByRaw() nhận chuỗi SQL và mảng binding ở tham số thứ hai.

Chú ý: Raw expression không được bảo vệ khỏi SQL injection. Với $status = "todo' or '1'='1" từ người dùng, whereRaw("status = '$status'") trả về cả 10 task, còn whereRaw('status = ?', [$status]) trả về 0. Luôn dùng dấu ? và binding.

Trang thống kê cho TaskFlow.

Mình tạo single action controller (Bài 5) bằng php artisan make:controller TaskStatsController --invokable. Mỗi cột đếm là một selectRaw(), leftJoin để project chưa có task vẫn hiện số 0:

- app/Http/Controllers/TaskStatsController.php

use Illuminate\Support\Facades\DB;
use Illuminate\View\View;

public function __invoke(): View
{
    $stats = DB::table('projects')
        ->leftJoin('tasks', 'tasks.project_id', '=', 'projects.id')
        ->select('projects.id', 'projects.name')
        // đếm task theo từng trạng thái, giá trị truyền qua binding
        ->selectRaw('count(case when tasks.status = ? then 1 end) as todo', ['todo'])
        ->selectRaw('count(case when tasks.status = ? then 1 end) as doing', ['doing'])
        ->selectRaw('count(case when tasks.status = ? then 1 end) as done', ['done'])
        ->selectRaw('count(tasks.id) as total')
        ->groupBy('projects.id', 'projects.name')
        ->orderBy('projects.id')
        ->get();

    return view('stats', compact('stats'));
}

Mình thêm route Route::get('/stats', TaskStatsController::class)->name('stats') (nhớ use App\Http\Controllers\TaskStatsController; trong routes/web.php), link "Thống kê" trên menu và view in mỗi $row một dòng:

- resources/views/stats.blade.php (trích)

@foreach ($stats as $row)
    <tr class="border-b">
        <td class="p-2">{{ $row->name }}</td>
        <td class="p-2">{{ $row->todo }}</td>
        <td class="p-2">{{ $row->doing }}</td>
        <td class="p-2">{{ $row->done }}</td>
        <td class="p-2">{{ $row->total }}</td>
    </tr>
@endforeach

Kết quả:

Project	Todo	Doing	Done	Tổng
Website toidicode.com	2	1	1	4
TaskFlow API	1	1	1	3
Du lịch Đà Lạt	1	1	1	3

Trang /projects vẫn đọc từ session, Bài 15 sẽ chuyển sang model.

6. Xem câu SQL được tạo ra.

toSql() trả về câu SQL còn dấu ?, toRawSql() trả về câu đã thay giá trị binding vào:

$query = DB::table('tasks')->where('status', 'todo')->orderBy('due_date');

$query->toSql();    // select * from "tasks" where "status" = ? order by "due_date" asc
$query->toRawSql(); // select * from "tasks" where "status" = 'todo' order by "due_date" asc

Muốn ghi lại mọi câu query, các bạn đăng ký DB::listen() trong method boot của AppServiceProvider (như rate limiter ở Bài 4):

use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\Log;

// trong boot()
DB::listen(function (QueryExecuted $query) {
    Log::debug($query->toRawSql(), ['ms' => $query->time]);
});

Log sẽ có cả các câu query Laravel tự chạy (bảng sessions), chỉ bật khi debug.

7. Lời kết.

Như vậy chúng ta đã tìm hiểu xong Query Builder trong Laravel 13. Các bạn xem thêm tài liệu về Query Builder. Bài sau mình sẽ giới thiệu về Eloquent Model, làm việc với database qua class Project, Task.