Laravel 中使用帶有條件聚合函式計算總數

4pmzzzzzzzzzz發表於2023-01-13
Laravel函式

假如有電子郵件訂閱服務，希望顯示訂閱者的詳情統計頁面如下顯示

訂閱者總數 確認(confirmed) 未經證實(unconfirmed) 取消(cancelled) 拒絕(bounced)
200 150 50 10 5

出於本文的目的，假設我們有一個subscribers包含以下格式資料的資料庫表：

name email status
小明 adam@hotmeteor.com confirmed
小紅 taylor@laravel.com unconfirmed
小軍 jonathan@reinink.ca cancelled
小花 adam.wathan@gmail.com bounced

大部分人的做法：

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

上面這樣肯定會產生五條語句，這樣做肯定是很不好。所以嘗試最佳化一下，會使用另一個方法解決執行多條語句的問題：

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

上面先獲取全部訂閱者資料，然後再對這個結果集進行條件統計，使用集合.模型多條資料查詢返回Illuminate\Database\Eloquent\Collection。這樣的方法，只適合再資料量不大的時候使用,如果我們的應用程式有數千或數百萬訂閱者，處理的時間會很慢，並且會使用大量記憶體。

實際上有一種非常簡單的方法可以查詢計算這些總數。訣竅是將條件放在聚合函式中。下面是一個 SQL 示例：

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 = 'bounced' then 1 end) as bounced
from subscribers

 total | confirmed | unconfirmed | cancelled | bounced
-------+-----------+-------------+-----------+---------
   200 |       150 |          50 |        30 |      25

以下是在 Laravel 中使用查詢構建器編寫此查詢：

$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 = 'bounced' then 1 end) as bounced")
    ->first();

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

表遷移建立 boolean 欄位 , model定義屬於轉換 此處不用model為程式碼示例,可自行替換為model

如果使用boolean當欄位列，將更容易，比如要查詢subscribers表中的使用者是否為擁有不同的角色許可權。假設subscribers表中有is_admin、is_treasurer、is_editor、is_manager、欄位

$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();

這是因為聚合函式count忽略null列。與PHP中false || null返回false不同，在SQL（以及JavaScript）中，它返回null。基本上，如果A可以強制為真，則A || B返回值A；否則，返回B。

這段話如果沒理解，就看我下面說明：
使用laravel的boolean列，實際資料表裡欄位為tinyint,值為0(false)與1(true), 比如
小明的is_admin欄位為1(true),count(is_admin or null)可以看作表示為(1 or null)，這A為真 返回A,最終sql為count(is_admin)。
反之則是如is_admin欄位為0(false)，最終sql為count(null),則忽略此列

//PHP  返回 false
var_dump(0 || null) 

//JavaScript 返回 null
console.log(0 || null)

//SQL 返回 null
SELECT (0 or null) as result

翻譯原文：本文只是翻譯一下大概意思,作為自己單純記錄使用

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章