[TOC] # 查詢構造器 ## 簡介 Laravel 的查詢構造器使用 PDO 參數綁定來保護您的應用程序免受 SQL 注入攻擊。 ## 獲取結果 ### 從一個數據表中獲取所有行 ``` use Illuminate\Support\Facades\DB; $users = DB::table('users')->get(); // 返回包含 Illuminate\Support\Collection 的結果，每個結果都是 StdClass 對象的實例 foreach ($users as $user) { echo $user->name; } ``` ### 從數據表中獲取單行或單列 ``` // 返回 StdClass 對象 $user = DB::table('users')->where('name', 'John')->first(); echo $user->name; // 返回單個字段的值 $email = DB::table('users')->where('name', 'John')->value('email'); ``` ### 獲取一列的值 ``` // 返回單列值的集合 $titles = DB::table('roles')->pluck('title'); foreach ($titles as $title) { echo $title; } // 返回的集合中指定字段的自定義鍵值 $roles = DB::table('roles')->pluck('title', 'name'); foreach ($roles as $name => $title) { echo $title; } ``` ### 分塊結果 ``` DB::table('users')->orderBy('id')->chunk(100, function ($users) { foreach ($users as $user) { // } }); // 終止查詢 DB::table('users')->orderBy('id')->chunk(100, function ($users) { // Process the records... return false; }); // 使用 chunkById 方法根據記錄的主鍵自動對結果進行分頁，保證查詢結果 DB::table('users')->where('active', false) ->chunkById(100, function ($users) { foreach ($users as $user) { DB::table('users') ->where('id', $user->id) ->update(['active' => true]); } }); ``` > 注意：在塊的回調里面更新或刪除記錄時，對主鍵或外鍵的任何更改都可能影響塊查詢。 這可能會導致記錄沒有包含在分塊結果中。 ### 聚合 ``` // count, max，min，avg，sum $users = DB::table('users')->count(); $price = DB::table('orders')->max('price'); $price = DB::table('orders') ->where('finalized', 1) ->avg('price'); ``` ### 判斷記錄是否存在 ``` return DB::table('orders')->where('finalized', 1)->exists(); return 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(); ``` ## 原生表達式 ``` // 使用 DB::raw 創建原生表達式 $users = DB::table('users') ->select(DB::raw('count(*) as user_count, status')) ->where('status', '<>', 1) ->groupBy('status') ->get(); ``` >[success] 注意：原生表達式將會被當做字符串注入到查詢中，因此你應該小心使用，避免創建 SQL 注入的漏洞。 ### 原生方法 ``` // selectRaw 方法代替 select(DB::raw(...))。第二個參數是可選項，值為綁定參數的數組 $orders = DB::table('orders') ->selectRaw('price * ? as price_with_tax', [1.0825]) ->get(); // whereRaw 和 orWhereRaw 方法將原生的 where 注入到的查詢中。 $orders = DB::table('orders') ->whereRaw('price > IF(state = "TX", ?, 100)', [200]) ->get(); // havingRaw 和 orHavingRaw 方法用于將原生字符串設置為 having 語句的值 $orders = DB::table('orders') ->select('department', DB::raw('SUM(price) as total_sales')) ->groupBy('department') ->havingRaw('SUM(price) > ?', [2500]) ->get(); // orderByRaw 方法用于將原生字符串設置為 order by 子句的值 $orders = DB::table('orders') ->orderByRaw('updated_at - created_at DESC') ->get(); ``` ## Join ``` // Inner Join Clause $users = DB::table('users') ->join('contacts', 'users.id', '=', 'contacts.user_id') ->join('orders', 'users.id', '=', 'orders.user_id') ->select('users.*', 'contacts.phone', 'orders.price') ->get(); // Left Join 語句 $users = DB::table('users') ->leftJoin('posts', 'users.id', '=', 'posts.user_id') ->get(); $users = DB::table('users') ->rightJoin('posts', 'users.id', '=', 'posts.user_id') ->get(); // Cross Join 語句 $users = DB::table('sizes') ->crossJoin('colours') ->get(); // 高級 Join 語句 DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id')->orOn(...); }) ->get(); DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id') ->where('contacts.user_id', '>', 5); }) ->get(); // 子連接查詢 // 使用 joinSub，leftJoinSub，rightJoinSub 方法關聯一個查詢作為子查詢。 // 他們每一種方法都會接收三個參數：子查詢，表別名和定義關聯字段的閉包。 $latestPosts = DB::table('posts') ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at')) ->where('is_published', true) ->groupBy('user_id'); $users = DB::table('users') ->joinSub($latestPosts, 'latest_posts', function($join) { $join->on('users.id', '=', 'latest_posts.user_id'); })->get(); ``` ## Union ``` // union，unionAll $first = DB::table('users') ->whereNull('first_name'); $users = DB::table('users') ->whereNull('last_name') ->union($first) ->get(); ``` ## Where 語句 ### 簡單的 Where 語句 ``` // 調用 where 最基本的方式是需要傳遞三個參數： // 第一個參數是列名， // 第二個參數是任意一個數據庫系統支持的運算符， // 第三個是該列要比較的值。 $users = DB::table('users')->where('votes', '=', 100)->get(); // 相等比較 $users = DB::table('users')->where('votes', 100)->get(); // 其他的運算符 $users = DB::table('users') ->where('votes', '>=', 100) ->get(); $users = DB::table('users') ->where('votes', '<>', 100) ->get(); $users = DB::table('users') ->where('name', 'like', 'T%') ->get(); // 數組條件 $users = DB::table('users')->where([ ['status', '=', '1'], ['subscribed', '<>', '1'], ])->get(); ``` ### Or 語句 ``` $users = DB::table('users') ->where('votes', '>', 100) ->orWhere('name', 'John') ->get(); ``` ### 其他 Where 語句 ``` // whereBetween $users = DB::table('users')->whereBetween('votes', [1, 100])->get(); // whereNotBetween $users = DB::table('users')->whereNotBetween('votes', [1, 100])->get(); // whereIn $users = DB::table('users')->whereIn('id', [1, 2, 3])->get(); // whereNotIn $users = DB::table('users')->whereNotIn('id', [1, 2, 3])->get(); // whereNull $users = DB::table('users')->whereNull('updated_at')->get(); // whereNotNull $users = DB::table('users')->whereNotNull('updated_at')->get(); // whereDate $users = DB::table('users')->whereDate('created_at', '2018-09-08')->get(); // whereMonth $users = DB::table('users')->whereMonth('created_at', '9')->get(); // whereDay $users = DB::table('users')->whereDay('created_at', '8')->get(); // whereYear $users = DB::table('users')->whereYear('created_at', '2018')->get(); // whereTime $users = DB::table('users')->whereTime('created_at', '=', '11:20:45')->get(); // whereColumn，比較兩個字段的值是否相等 $users = DB::table('users')->whereColumn('first_name', 'last_name')->get(); // 使用比較運算符 $users = DB::table('users')->whereColumn('updated_at', '>', 'created_at')->get(); // 傳遞數組 用 and 運算符鏈接 $users = DB::table('users') ->whereColumn([ ['first_name', '=', 'last_name'], ['updated_at', '>', 'created_at'] ])->get(); ``` ### 參數分組 ``` // 生成SQL：select * from users where name = 'John' and (votes > 100 or title = 'Admin') DB::table('users') ->where('name', '=', 'John') ->where(function ($query) { $query->where('votes', '>', 100) ->orWhere('title', '=', 'Admin'); }) ->get(); ``` ### Where Exists 語句 ``` DB::table('users') ->whereExists(function ($query) { $query->select(DB::raw(1)) ->from('orders') ->whereRaw('orders.user_id = users.id'); }) ->get(); // 生成SQL select * from users where exists ( select 1 from orders where orders.user_id = users.id ) ``` ### JSON Where 語句 Laravel 也支持查詢 JSON 類型的字段（僅在對 JSON 類型支持的數據庫上）。目前，本特性僅支持 MySQL 5.7、PostgreSQL、SQL Server 2016 以及 SQLite 3.9.0 (with the[JSON1 extension](https://www.sqlite.org/json1.html))。使用`->`操作符查詢 JSON 數據： ``` $users = DB::table('users') ->where('options->language', 'en') ->get(); $users = DB::table('users') ->where('preferences->dining->meal', 'salad') ->get(); // 使用 whereJsonContains 來查詢 JSON 數組 $users = DB::table('users') ->whereJsonContains('options->languages', 'en') ->get(); // MySQL 和 PostgreSQL 的 whereJsonContains 可以支持多個值 $users = DB::table('users') ->whereJsonContains('options->languages', ['en', 'de']) ->get(); // 使用 whereJsonLength 來查詢 JSON 數組的長度 $users = DB::table('users') ->whereJsonLength('options->languages', 0) ->get(); $users = DB::table('users') ->whereJsonLength('options->languages', '>', 1) ->get(); ``` ## Ordering, Grouping, Limit, & Offset ### orderBy ``` $users = DB::table('users') ->orderBy('name', 'desc') ->get(); ``` ### latest / oldest ``` // latest、oldest 方法通過日期排序。 // 默認使用 created_at 列作為排序依據，也可以傳遞自定義的列名。 $user = DB::table('users') ->latest() ->first(); ``` ### inRandomOrder ``` // 隨機排序，使用此方法隨機找到一個用戶 $randomUser = DB::table('users') ->inRandomOrder() ->first(); ``` ### groupBy / having ``` $users = DB::table('users') ->groupBy('account_id') ->having('account_id', '>', 100) ->get(); // 多個 groupBy 參數 $users = DB::table('users') ->groupBy('first_name', 'status') ->having('account_id', '>', 100) ->get(); ``` ### skip / take ``` // 限制結果的返回數量，或跳過指定數量的結果。 $users = DB::table('users')->skip(10)->take(5)->get(); $users = DB::table('users') ->offset(10) ->limit(5) ->get(); ``` ## 條件語句 ``` // when 方法只有在第一個參數為 true 的時候才執行閉包。 $role = $request->input('role'); $users = DB::table('users') ->when($role, function ($query, $role) { return $query->where('role_id', $role); }) ->get(); // 第三個參數可選，在第一個參數為 false 的情況下執行。 $sortBy = null; $users = DB::table('users') ->when($sortBy, function ($query, $sortBy) { return $query->orderBy($sortBy); }, function ($query) { return $query->orderBy('name'); }) ->get(); ``` ## 插入 ``` // 插入單條記錄 DB::table('users')->insert( ['email' => 'john@example.com', 'votes' => 0] ); // 插入多條記錄 DB::table('users')->insert([ ['email' => 'taylor@example.com', 'votes' => 0], ['email' => 'dayle@example.com', 'votes' => 0] ]); ``` ### 自增 ID ``` $id = DB::table('users')->insertGetId( ['email' => 'john@example.com', 'votes' => 0] ); ``` > 當使用 PostgreSQL 時，`insertGetId`方法將默認把`id`作為自動遞增字段的名稱。如果你要從其他「序列」來獲取 ID ，則可以將字段名稱作為第二個參數傳遞給`insertGetId`方法。 ## 更新 ``` DB::table('users') ->where('id', 1) ->update(['votes' => 1]); ``` ### 更新或者新增 ``` // updateOrInsert 方法將首先嘗試使用第一個參數的鍵和值對來查找匹配的數據庫記錄。 // 如果記錄存在，則使用第二個參數中的值去更新記錄。 // 如果找不到記錄，將插入一個新記錄，更新的數據是兩個數組的集合。 DB::table('users') ->updateOrInsert( ['email' => 'john@example.com', 'name' => 'John'], ['votes' => '2'] ); ``` ### 更新 JSON 字段 ``` // 只能支持 MySQL 5.7+ DB::table('users') ->where('id', 1) ->update(['options->enabled' => true]); ``` ### 自增與自減 ``` DB::table('users')->increment('votes'); DB::table('users')->increment('votes', 5); DB::table('users')->decrement('votes'); DB::table('users')->decrement('votes', 5); // 增加更新字段 DB::table('users')->increment('votes', 1, ['name' => 'John']); ``` ## 刪除 ``` // 刪除記錄 DB::table('users')->delete(); DB::table('users')->where('votes', '>', 100)->delete(); // 清空表，重置自增 ID 為零 DB::table('users')->truncate(); ``` ## 悲觀鎖 ``` // 共享鎖 DB::table('users')->where('votes', '>', 100)->sharedLock()->get(); // 排它鎖 DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get(); ```