什麼是查詢建構器
Laravel 的查詢建構器（Query Builder）是以流暢介面建構並執行資料庫查詢的機制。從DB facade 的 table() 方法開始，透過方法鏈組合查詢。
use Illuminate\Support\Facades\DB;
$users = DB::table('users')->get();
查詢建構器可在 Laravel 支援的所有資料庫（MySQL、MariaDB、PostgreSQL、SQLite、SQL Server）上運作。切換資料庫時可使用相同的程式碼。
與 Eloquent 的分工
| 情境 | 建議 |
|---|---|
| 需要 model 與 relation | Eloquent |
| 複雜的集計或報表 | 查詢建構器 |
| 對效能要求高的大量資料處理 | 查詢建構器 |
| 對既有資料表的簡單操作 | 查詢建構器 |
| 在 migration 或 seeder 中處理 | 查詢建構器 |
取得資料
全部取得
$users = DB::table('users')->get();
foreach ($users as $user) {
echo $user->name;
}
get() 會回傳 Illuminate\Support\Collection。每筆記錄是 PHP 的 stdClass 物件。
取得單筆
// 取得第一筆（找不到時為 null）
$user = DB::table('users')->where('name', '山田太郎')->first();
// 找不到時擲出例外（自動回傳 404）
$user = DB::table('users')->where('name', '山田太郎')->firstOrFail();
// 只取特定欄位的值
$email = DB::table('users')->where('name', '山田太郎')->value('email');
// 依 ID 取得
$user = DB::table('users')->find(3);
以清單取得特定欄位值
// email 欄位值的 collection
$emails = DB::table('users')->pluck('email');
// 以 name 為 key、email 為 value 的關聯 collection
$emailByName = DB::table('users')->pluck('email', 'name');
大量資料的分塊處理
use Illuminate\Support\Collection;
// 每 100 筆處理
DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
foreach ($users as $user) {
// 處理...
}
});
// 邊處理邊更新時，使用 chunkById
DB::table('users')->where('active', false)
->chunkById(100, function (Collection $users) {
foreach ($users as $user) {
DB::table('users')
->where('id', $user->id)
->update(['active' => true]);
}
});
在 chunk 處理中要更新或刪除記錄時，請使用
chunkById() 而不是 chunk()。chunk() 可能會產生記錄錯位。串流（LazyCollection）
DB::table('users')->orderBy('id')->lazy()->each(function (object $user) {
// 逐筆處理
});
集計
$count = DB::table('users')->count();
$maxAge = DB::table('users')->max('age');
$minAge = DB::table('users')->min('age');
$avgAge = DB::table('users')->avg('age');
$total = DB::table('orders')->sum('amount');
// 條件式集計
$avgPremium = DB::table('orders')
->where('plan', 'premium')
->avg('amount');
記錄存在確認
if (DB::table('orders')->where('finalized', 1)->exists()) {
// 記錄存在
}
if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
// 記錄不存在
}
SELECT 子句
// 指定要取得的欄位
$users = DB::table('users')
->select('name', 'email as user_email')
->get();
// 去除重複
$users = DB::table('users')->distinct()->get();
// 之後追加欄位
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();
WHERE 子句
基本條件
// 等值條件（可省略 =）
$users = DB::table('users')->where('votes', 100)->get();
// 指定比較運算子
$users = DB::table('users')->where('votes', '>=', 100)->get();
$users = DB::table('users')->where('name', 'like', '山田%')->get();
// 多重條件（AND）
$users = DB::table('users')
->where('status', 'active')
->where('age', '>', 20)
->get();
// OR 條件
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', '山田太郎')
->get();
條件的分組
use Illuminate\Database\Query\Builder;
// 將 OR 條件分組並與 AND 結合
$users = DB::table('users')
->where('active', true)
->where(function (Builder $query) {
$query->where('role', 'admin')
->orWhere('role', 'moderator');
})
->get();
// WHERE active = 1 AND (role = 'admin' OR role = 'moderator')
whereIn / whereBetween / whereNull
// IN 子句
$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();
$users = DB::table('users')
->whereNotIn('id', [1, 2, 3])
->get();
// BETWEEN 子句
$users = DB::table('users')
->whereBetween('age', [20, 40])
->get();
// NULL 判定
$users = DB::table('users')->whereNull('deleted_at')->get();
$users = DB::table('users')->whereNotNull('email_verified_at')->get();
whereLike（樣式比對）
// 預設不區分大小寫
$users = DB::table('users')
->whereLike('name', '%山田%')
->get();
// 區分大小寫
$users = DB::table('users')
->whereLike('name', '%Yamada%', caseSensitive: true)
->get();
whereAny / whereAll（多欄位相同條件）
// 任一欄位符合 LIKE 條件
$users = DB::table('users')
->where('active', true)
->whereAny(['name', 'email', 'bio'], 'like', '%Laravel%')
->get();
// 所有欄位皆符合 LIKE 條件
$posts = DB::table('posts')
->whereAll(['title', 'content'], 'like', '%Laravel%')
->get();
whereNullSafeEquals（NULL 安全等值比較）
whereNullSafeEquals 與 orWhereNullSafeEquals 在將欄位值與指定值比較時，將兩個 NULL 值視為相等。
一般的 = 運算子中 NULL = NULL 會是 false，而 whereNullSafeEquals 則將 NULL 之間判定為相等。對應到 MySQL 的 <=> 運算子，以及 PostgreSQL 的 IS NOT DISTINCT FROM。
$lastLoginIp = $request->input('last_login_ip');
// 即使 $lastLoginIp 為 null，也能取得 last_login_ip IS NULL 的使用者
$users = DB::table('users')
->whereNullSafeEquals('last_login_ip', $lastLoginIp)
->get();
一般的
where('column', null) 會轉為 WHERE column IS NULL，而 whereNullSafeEquals('column', $value) 不論綁定的值是否為 null 都能一致運作。特別適用於使用者輸入值可能為 null 的情境。JOIN
// INNER JOIN
$users = DB::table('users')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.name', 'orders.amount')
->get();
// LEFT JOIN
$users = DB::table('users')
->leftJoin('orders', 'users.id', '=', 'orders.user_id')
->get();
// 多資料表 JOIN
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.amount')
->get();
子查詢 JOIN
// 使用子查詢的 JOIN
$latestOrders = DB::table('orders')
->select('user_id', DB::raw('MAX(created_at) as last_order_at'))
->groupBy('user_id');
$users = DB::table('users')
->joinSub($latestOrders, 'latest_orders', function ($join) {
$join->on('users.id', '=', 'latest_orders.user_id');
})
->get();
排序、分組、限制
// 排序
$users = DB::table('users')
->orderBy('name', 'asc')
->get();
// 以多個欄位排序
$users = DB::table('users')
->orderBy('last_name')
->orderBy('first_name', 'desc')
->get();
// 隨機順序
$users = DB::table('users')->inRandomOrder()->get();
// 分組
$orders = DB::table('orders')
->select('status', DB::raw('COUNT(*) as count'))
->groupBy('status')
->get();
// HAVING 子句
$orders = DB::table('orders')
->select('user_id', DB::raw('SUM(amount) as total'))
->groupBy('user_id')
->having('total', '>', 10000)
->get();
// LIMIT 與 OFFSET
$users = DB::table('users')
->skip(10) // OFFSET
->take(5) // LIMIT
->get();
子查詢
// WHERE 子句的子查詢
$activeUsers = DB::table('users')->select('id')->where('is_active', 1);
$comments = DB::table('comments')
->whereIn('user_id', $activeUsers)
->get();
// SELECT 子句的子查詢
$users = DB::table('users')
->select('name')
->selectSub(function ($query) {
$query->from('orders')
->selectRaw('COUNT(*)')
->whereColumn('orders.user_id', 'users.id');
}, 'order_count')
->get();
Raw 表達式
Raw 表達式會作為 SQL 字串直接插入查詢中。直接傳入使用者輸入會有 SQL injection 風險。請務必使用綁定安全地撰寫。
// DB::raw() — 嵌入任意 SQL 表達式
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->groupBy('status')
->get();
// selectRaw — 在 SELECT 子句追加 Raw 表達式
$orders = DB::table('orders')
->selectRaw('price * ? as price_with_tax', [1.10])
->get();
// whereRaw — 在 WHERE 子句追加 Raw 表達式
$orders = DB::table('orders')
->whereRaw('price > IF(state = "JP", ?, 100)', [500])
->get();
// havingRaw — 在 HAVING 子句追加 Raw 表達式
$orders = DB::table('orders')
->select('department', DB::raw('SUM(amount) as total'))
->groupBy('department')
->havingRaw('SUM(amount) > ?', [100000])
->get();
// orderByRaw — 在 ORDER BY 子句追加 Raw 表達式
$orders = DB::table('orders')
->orderByRaw('updated_at - created_at DESC')
->get();
INSERT / UPDATE / DELETE
INSERT
// 插入單筆
DB::table('users')->insert([
'email' => '[email protected]',
'name' => '山田太郎',
]);
// 插入多筆
DB::table('users')->insert([
['email' => '[email protected]', 'name' => '山田太郎'],
['email' => '[email protected]', 'name' => '鈴木花子'],
]);
// 插入後取得 AUTO_INCREMENT 的 ID
$id = DB::table('users')->insertGetId([
'email' => '[email protected]',
'name' => '佐藤次郎',
]);
UPSERT（INSERT OR UPDATE）
// 存在則更新，不存在則插入
DB::table('users')->upsert(
[
['email' => '[email protected]', 'name' => '山田太郎', 'votes' => 5],
['email' => '[email protected]', 'name' => '鈴木花子', 'votes' => 10],
],
uniqueBy: ['email'], // 重複檢查的欄位
update: ['name', 'votes'] // 要更新的欄位
);
UPDATE
// 條件式更新
$affected = DB::table('users')
->where('id', 1)
->update(['name' => '山田一郎', 'updated_at' => now()]);
// increment / decrement
DB::table('users')->where('id', 1)->increment('votes'); // +1
DB::table('users')->where('id', 1)->increment('votes', 5); // +5
DB::table('users')->where('id', 1)->decrement('votes'); // -1
DB::table('users')->where('id', 1)->decrement('balance', 100); // -100
DELETE
// 條件式刪除
$deleted = DB::table('users')->where('status', 'inactive')->delete();
// 清空整個資料表（重設 AUTO_INCREMENT）
DB::table('users')->truncate();
條件式查詢（when）
要動態套用查詢條件時，使用when() 可讓條件分支寫得更整潔。
$status = request('status');
$sortBy = request('sort', 'name');
$users = DB::table('users')
->when($status, function ($query, $status) {
$query->where('status', $status);
})
->when($sortBy === 'email', function ($query) {
$query->orderBy('email');
}, function ($query) {
$query->orderBy('name');
})
->get();
除錯
// 確認產生的 SQL
$sql = DB::table('users')->where('active', true)->toSql();
// "select * from `users` where `active` = ?"
// 同時確認 SQL 與 bindings
$bindings = DB::table('users')->where('active', true)->getBindings();
// 執行查詢並 dump（繼續執行）
DB::table('users')->where('active', true)->dump();
// 執行查詢、dump 並結束
DB::table('users')->where('active', true)->dd();
dd() 於除錯時方便，但絕不可用於正式環境。使用 toSql() 與 getBindings() 確認 SQL 與 bindings 較為安全。總結
常用方法一覽
常用方法一覽
| 方法 | 說明 |
|---|---|
get() | 全部取得（Collection） |
first() | 取得第一筆 |
find($id) | 依 ID 取得 |
value($column) | 取得單筆欄位值 |
pluck($column) | 取得欄位值清單 |
count() | 筆數 |
sum($col) | 加總 |
avg($col) | 平均 |
max($col) | 最大值 |
min($col) | 最小值 |
exists() | 存在確認 |
insert([...]) | 插入 |
update([...]) | 更新 |
delete() | 刪除 |
chunk($n, fn) | 分塊處理 |
when($cond, fn) | 條件式查詢 |
toSql() | 檢視產生的 SQL |
查詢建構器 vs Eloquent
查詢建構器 vs Eloquent
查詢建構器比 Eloquent 更低階，回傳的不是 model 實例而是
stdClass。
當不需要 relation 或 model 事件（observer）時，查詢建構器更簡潔快速。// Eloquent：回傳 User model 的實例
$users = User::where('active', true)->get();
echo $users[0]->name; // User 物件
// 查詢建構器：回傳 stdClass
$users = DB::table('users')->where('active', true)->get();
echo $users[0]->name; // stdClass 物件