Tính tổng có điều kiện trong Laravel

Trong quá trình thiết kế web cho khách hàng, một trong những truy vấn phổ biến nhất mà QMAS hay gặp phải đó là tạo các truy vấn tính toán tổng số lượng theo điều kiện. Liệu bạn có hỏi: truy vấn này đâu có gì phức tạp. Đúng vậy, đây là một truy vấn rất bình thường. Tuy nhiên, không phải ai cũng biết cách để tối ưu nó sao cho đạt hiệu quả cao nhất.

Hãy thử lấy một ví dụ: khách hàng của bạn có một dịch vụ đăng ký nhận email theo dõi và bạn muốn hiển thị các tổng khác nhau dựa trên trạng thái đăng ký.

Đối với mục đích của bài viết này, giả sử rằng chúng ta có một bảng cơ sở dữ liệu người đăng ký với dữ liệu ở định dạng sau:

Tên	Email	Trạng thái
Nguyễn Văn A	nguyenvana@gmail.com	confirmed
Trần Thị B	tranthib@gmail.com	unconfirmed
Phạm Đức C	phamducc@hotmail.com	cancelled
Tống Văn D	tongvand@yahoo.com	rejected

Lý tưởng nhất là chúng ta muốn tính toán các giá trị này bằng một truy vấn duy nhất. Tuy nhiên, nếu bạn không chắc chắn về cách làm điều đó, bạn vẫn có thể nhận kết quả chính xác bằng cách thực hiện nhiều truy vấn dạng như này:

$total = Subscriber::count();
$confirmed = Subscriber::where('status', 'confirmed')->count();
$unconfirmed = Subscriber::where('status', 'unconfirmed')->count();
$cancelled = Subscriber::where('status', 'cancelled')->count();
$rejected= Subscriber::where('status', 'rejected')->count();

Nhưng, tất nhiên, điều này sẽ khiến bạn mất tới 5 truy vấn, mà chúng ta biết rằng nhiều truy vấn luôn không phải là phương án tốt nhất. Vì vậy, bạn quyết định sẽ “ăn gian” một chút:

$subscribers = Subscriber::all();

$total = $subscribers->count();
$confirmed = $subscribers->where('status', 'confirmed')->count();
$unconfirmed = $subscribers->where('status', 'unconfirmed')->count();
$cancelled = $subscribers->where('status', 'cancelled')->count();
$rejected= $subscribers->where('status', 'rejected')->count();

Với cách này, bạn cũng chỉ mất một truy vấn duy nhất để lấy tất cả người đăng ký và sau đó thực hiện tính toán trên kết quả thu được. Vấn đề là, cách tiếp cận này trong đa số trường hợp còn tệ hơn đáng kể so với giải pháp đầu tiên. Nếu ứng dụng của bạn có hàng nghìn hoặc hàng triệu người đăng ký, thời gian xử lý tất cả các bản ghi sẽ rất chậm và nhiều khả năng bạn thậm chí còn gặp lỗi tràn bộ nhớ.

Truy vấn tính tổng có điều kiện

Thực ra có một cách thực sự đơn giản để tính toán được các tổng này bằng cách sử dụng một truy vấn duy nhất. Bí quyết là chúng ta sẽ đặt các điều kiện trong các hàm tính tổng của MySQL. Đây là một ví dụ:

select
  count(*) as total,
  count(case when status = 'confirmed' then 1 end) as confirmed,
  count(case when status = 'unconfirmed' then 1 end) as unconfirmed,
  count(case when status = 'cancelled' then 1 end) as cancelled,
  count(case when status = 'rejected' then 1 end) as rejected
from subscribers

 total | confirmed | unconfirmed | cancelled | rejected
-------+-----------+-------------+-----------+---------
   500 |       350 |         100 |        30 |       20

Truy vấn trên có thể được viết dễ dàng trong Laravel như sau:

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw("count(case when status = 'confirmed' then 1 end) as confirmed")
    ->selectRaw("count(case when status = 'unconfirmed' then 1 end) as unconfirmed")
    ->selectRaw("count(case when status = 'cancelled' then 1 end) as cancelled")
    ->selectRaw("count(case when status = 'rejected' then 1 end) as rejected")
    ->first();

<div>Total: {{ $totals->total }}</div>
<div>Confirmed: {{ $totals->confirmed }}</div>
<div>Unconfirmed: {{ $totals->unconfirmed }}</div>
<div>Cancelled: {{ $totals->cancelled }}</div>
<div>Rejected: {{ $totals->rejected}}</div>

Tốt hơn nhiều rồi, phải không?

Cột Boolean

Cách tiếp cận này thậm chí còn dễ dàng hơn nếu bạn đang sử dụng các cột boolean trong SQL. Một trường hợp điển hình cho việc này là tính tổng các chức danh khác nhau trong một ứng dụng.

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw('count(is_admin or null) as admins')
    ->selectRaw('count(is_treasurer or null) as treasurers')
    ->selectRaw('count(is_editor or null) as editors')
    ->selectRaw('count(is_manager or null) as managers')
    ->first();

Điều này hoạt động vì hàm count() của SQL bỏ qua các cột rỗng. Không giống như trong PHP, nơi mà false hoặc null trả về false, thì trong SQL nó sẽ trả về null. Về cơ bản, trong SQL A or B sẽ trả về giá trị A nếu A có thể bị ép buộc thành true; nếu không, nó trả về B.

Mệnh đề filter trong PostgreSQL

Cuối cùng, nếu bạn đang sử dụng PostgreSQL, bạn cũng có thể sử dụng các mệnh đề filter để làm được điều này. Các mệnh đề filter rất tuyệt vì chúng cho phép bạn viết các điều kiện của mình bằng cách sử dụng mệnh đề where thông thường. Hơn nữa, dựa trên thực tế làm việc, tôi thấy rằng các mệnh đề filter thực sự nhanh hơn các cách tiếp cận đã được đề cập ở trên.

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw('count(*) filter (where is_admin) as admins')
    ->selectRaw('count(*) filter (where is_treasurer) as treasurers')
    ->selectRaw('count(*) filter (where is_editor) as editors')
    ->selectRaw('count(*) filter (where is_manager) as managers')
    ->first();

OK! Vậy là bạn đã hiểu nên tối ưu các truy vấn tính tổng theo điều kiện trên SQL như thế nào rồi đấy. Hãy đảm bảo tận dụng chúng vào lần tới khi cần thiết phải áp dụng trong dự án của mình!

Thân ái!