Làm chủ Common Table Expressions (CTE) trong Laravel Eloquent

· 4 min read
#laravel #database #eloquent #sql #performance

Common Table Expressions (CTEs) là các tập kết quả tạm thời mà bạn có thể tham chiếu trong các câu lệnh SELECT, INSERT, UPDATE hoặc DELETE. Chúng cực kỳ hữu ích để chia nhỏ các truy vấn phức tạp và xử lý các cấu trúc dữ liệu đệ quy (như danh mục đa cấp, comments).

Trong các phiên bản Laravel gần đây, Eloquent đã hỗ trợ native cho CTEs, giúp chúng ta không cần phải viết raw SQL strings cho các tình huống này nữa.

Tại sao nên dùng CTEs?

  1. Dễ đọc: Chia một subquery lồng nhau phức tạp thành các bước logic có tên gọi rõ ràng.
  2. Đệ quy: Dễ dàng truy vấn dữ liệu phân cấp (cha con, cây thư mục).
  3. Hiệu năng: Thường hiệu quả hơn việc xử lý logic bằng vòng lặp trong PHP.

Ví dụ cơ bản: Tìm User "VIP"

Giả sử bạn muốn tìm các user đã đặt hàng với tổng giá trị trên $1000.

Thay vì dùng subquery dài dòng, ta dùng CTE:

// Sử dụng CTE với query builder của Laravel

use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;

$users = DB::table('users')
    ->withExpression('big_spenders', function (Builder $query) {
        $query->select('user_id')
              ->from('orders')
              ->groupBy('user_id')
              ->havingRaw('SUM(total) > ?', [1000]);
    })
    ->join('big_spenders', 'users.id', '=', 'big_spenders.user_id')
    ->get();

CTE Đệ quy: Cây danh mục (Category Tree)

Đây là tính năng "ăn tiền" nhất của CTE. Giả sử bảng categories có id và parent_id. Làm sao để lấy một danh mục và tất cả con cháu của nó?

$categoryId = 1;

$categories = Category::query()
    ->withRecursiveExpression('descendants', function (Builder $query) use ($categoryId) {
        // Phần neo (Anchor member): chọn danh mục bắt đầu
        $query->from('categories')
              ->where('id', $categoryId);

        // Phần đệ quy (Recursive member): join với phần neo
        $query->unionAll(
            DB::table('categories')
                ->join('descendants', 'categories.parent_id', '=', 'descendants.id')
        );
    })
    ->from('descendants')
    ->get();

Chỉ với một truy vấn duy nhất, bạn lấy được toàn bộ cấu trúc cây.

Dùng CTE để làm sạch Logic

Thay vì tạo bảng tạm (temporary table) hoặc xử lý nặng trên PHP Collections, hãy dùng CTE để chuẩn bị dữ liệu.

Ví dụ: Tính thống kê ngày trước, sau đó mới query.

$stats = DB::table('daily_stats')
    ->withExpression('marketing_stats', function ($query) {
        $query->selectRaw('DATE(created_at) as date, COUNT(*) as clicks')
              ->from('clicks')
              ->groupBy('date');
    })
    ->withExpression('sales_stats', function ($query) {
        $query->selectRaw('DATE(created_at) as date, SUM(amount) as revenue')
              ->from('orders')
              ->groupBy('date');
    })
    ->select('marketing_stats.date', 'clicks', 'revenue')
    ->from('marketing_stats')
    ->join('sales_stats', 'marketing_stats.date', '=', 'sales_stats.date')
    ->get();

Lưu ý cho Laravel Developer

  1. Hỗ trợ Database: CTE hoạt động trên MySQL 8.0+, PostgreSQL, SQLite, và SQL Server. Hãy đảm bảo DB production của bạn hỗ trợ.
  2. Debugging: Sử dụng ->toSql() hoặc dd() để kiểm tra câu SQL sinh ra nếu gặp lỗi cú pháp.
  3. Tích hợp Eloquent: Mặc dù DB::table phổ biến, bạn hoàn toàn có thể dùng Model::withExpression(...) để trả về các đối tượng Model thay vì generic objects (stdClass).

Kết luận

CTEs biến những truy vấn "bất khả thi" thành đoạn code dễ quản lý và dễ đọc. Bằng cách tận dụng hỗ trợ native của Laravel, bạn có thể đẩy các logic xử lý dữ liệu phức tạp xuống tầng database - nơi chúng thuộc về, giữ cho ứng dụng PHP luôn nhanh và nhẹ.

Bình luận