查詢構造器 · Laravel 5.4 完全開發手冊

* * * * * [TOC] ## 簡介 Laravel 的數據庫查詢構造器提供了一個方便、流暢的接口，用來創建及運行數據庫查詢語句。它能用來執行應用程序中的大部分數據庫操作，且能在所有被支持的數據庫系統中使用。 Laravel 的查詢構造器使用 PDO 參數綁定，來保護你的應用程序免受 SQL 注入的攻擊。在綁定傳入字符串前不需要清理它們。 ## 獲取結果 #### 從數據表中獲取所有的數據列你可以使用?`DB`?facade 的?`table`?方法開始查詢。這個?`table`?方法針對查詢表返回一個查詢構造器實例，允許你在查詢時鏈式調用更多約束，并使用?`get`?方法獲取最終結果： ~~~ <?php namespace App\Http\Controllers; use Illuminate\Support\Facades\DB; use App\Http\Controllers\Controller; class UserController extends Controller { /** * Show a list of all of the application's users. * * @return Response */ public function index() { $users = DB::table('users')->get(); return view('user.index', ['users' => $users]); } } ~~~ `get`?方法會返回一個?`Illuminate\Support\Collection`?結果，其中每個結果都是一個 PHP?`StdClass`?對象的實例。您可以通過訪問列中對象的屬性訪問每個列的值： ~~~ foreach ($users as $user) { echo $user->name; } ~~~ #### 從數據表中獲取單個列或行如果你只需要從數據表中獲取一行數據，則可以使用?`first`?方法。這個方法將返回單個?`StdClass`?對象： ~~~ $user = DB::table('users')->where('name', 'John')->first(); echo $user->name; ~~~ 如果你不需要一整行數據，則可以使用?`value`?方法來從單條記錄中取出單個值。此方法將直接返回字段的值： ~~~ $email = DB::table('users')->where('name', 'John')->value('email'); ~~~ #### 獲取一列的值如果你想要獲取一個包含單個字段值的集合，可以使用?`pluck`?方法。在下面的例子中，我們將取出 roles 表中 title 字段的集合： ~~~ $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; } ~~~ ### 結果分塊如果你需要操作數千條數據庫記錄，可以考慮使用?`chunk`?方法。這個方法每次只取出一小塊結果，并會將每個塊傳遞給一個?`閉包`?處理。這個方法對于編寫數千條記錄的?[Artisan 命令](https://laravel-china.org/docs/laravel/5.4/artisan)?是非常有用的。例如，讓我們把?`users`?表進行分塊，每次操作 100 條數據： ~~~ DB::table('users')->orderBy('id')->chunk(100, function ($users) { foreach ($users as $user) { // } }); ~~~ 你可以從?`閉包`?中返回?`false`，以停止對后續分塊的處理： ~~~ DB::table('users')->orderBy('id')->chunk(100, function ($users) { // Process the records... return false; }); ~~~ ### 聚合查詢構造器也支持各種聚合方法，如?`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'); ~~~ ## Selects #### 指定一個 Select 子句當然，你并不會總是想從數據表中選出所有的字段。這時可使用?`select`?方法自定義一個?`select`?子句來查詢指定的字段： ~~~ $users = DB::table('users')->select('name', 'email as user_email')->get(); ~~~ `distinct`?方法允許你強制讓查詢返回不重復的結果： ~~~ $users = DB::table('users')->distinct()->get(); ~~~ 如果你已有一個查詢構造器實例，并且希望在現有的 select 子句中加入一個字段，則可以使用?`addSelect`?方法： ~~~ $query = DB::table('users')->select('name'); $users = $query->addSelect('age')->get(); ~~~ ## 原始表達式有時候你可能需要在查詢中使用原始表達式。這些表達式將會被當作字符串注入到查詢中，所以要小心避免造成 SQL 注入攻擊！要創建一個原始表達式，可以使用?`DB::raw`?方法： ~~~ $users = DB::table('users') ->select(DB::raw('count(*) as user_count, status')) ->where('status', '<>', 1) ->groupBy('status') ->get(); ~~~ ## Joins #### Inner Join 語法查詢構造器也可以編寫 join 語法。若要執行基本的「inner join」，你可以在查詢構造器實例上使用?`join`?方法。傳遞給?`join`?方法的第一個參數是你要 join 數據表的名稱，而其它參數則指定用來連接的字段約束。當然，如你所見，你可以在單個查找中連接多個數據表： ~~~ $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 語法如果你想用「left join」來代替「inner join」，請使用?`leftJoin`?方法。`leftJoin`?方法與?`join`?方法有著相同的用法： ~~~ $users = DB::table('users') ->leftJoin('posts', 'users.id', '=', 'posts.user_id') ->get(); ~~~ #### Cross Join 語法使用?`crossJoin`?方法和你想要交叉連接的表名來做「交叉連接」。交叉連接通過第一個表和連接表生成一個笛卡爾積： ~~~ $users = DB::table('sizes') ->crossJoin('colours') ->get(); ~~~ #### 高級 Join 語法你還可以指定更高級的 join 子句。讓我們傳遞一個`閉包`作為?`join`?方法的第二個參數來作為開始。此`閉包`將會收到一個?`JoinClause`?對象，讓你可以在?`join`?子句中指定約束： ~~~ DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id')->orOn(...); }) ->get(); ~~~ 如果你想要在連接中使用「where」風格的子句，則可以在連接中使用?`where`?和?`orWhere`?方法。這些方法將會比較值和對應的字段，而不是比較兩個字段： ~~~ DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id') ->where('contacts.user_id', '>', 5); }) ->get(); ~~~ ## Unions 查詢構造器也提供了一個快捷的方法來「合并」兩個查詢。例如，你可以先創建一個初始查詢，并使用?`union`?方法將它與第二個查詢進行合并： ~~~ $first = DB::table('users') ->whereNull('first_name'); $users = DB::table('users') ->whereNull('last_name') ->union($first) ->get(); ~~~ > {tip} 也可使用?`unionAll`?方法，它和?`union`?方法有著相同的用法。 ## Where 子句 #### 簡單的 Where 子句你可以在查詢構造器實例中使用?`where`?方法從而把?`where`?子句加入到這個查詢中。基本的?`where`?方法需要3個參數。第一個參數是字段的名稱。第二個參數是運算符，它可以是數據庫所支持的任何運算符。最后，第三個參數是要對字段進行評估的值。例如，這是一個要驗證「votes」字段的值等于 100 的查詢： ~~~ $users = DB::table('users')->where('votes', '=', 100)->get(); ~~~ 為方便起見，如果你只是想簡單的校驗某個字段等于一個指定的值，你可以直接將這個值作為第二個參數傳入?`where`方法： ~~~ $users = DB::table('users')->where('votes', 100)->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', 'T%') ->get(); ~~~ 你也可以通過一個條件數組做?`where`?的查詢： ~~~ $users = DB::table('users')->where([ ['status', '=', '1'], ['subscribed', '<>', '1'], ])->get(); ~~~ #### Or 語法你可以在查詢中加入?`or`?子句和 where 鏈式一起來約束查詢。`orWhere`?方法接收和?`where`?方法相同的參數： ~~~ $users = DB::table('users') ->where('votes', '>', 100) ->orWhere('name', 'John') ->get(); ~~~ #### 其它 Where 子句 **whereBetween** `whereBetween`?方法用來驗證字段的值介于兩個值之間： ~~~ $users = DB::table('users') ->whereBetween('votes', [1, 100])->get(); ~~~ **whereNotBetween** `whereNotBetween`?方法驗證字段的值?**不**?在兩個值之間： ~~~ $users = DB::table('users') ->whereNotBetween('votes', [1, 100]) ->get(); ~~~ **whereIn 與 whereNotIn** `whereIn`?方法驗證字段的值包含在指定的數組內： ~~~ $users = DB::table('users') ->whereIn('id', [1, 2, 3]) ->get(); ~~~ `whereNotIn`?方法驗證字段的值?**不**?包含在指定的數組內： ~~~ $users = DB::table('users') ->whereNotIn('id', [1, 2, 3]) ->get(); ~~~ **whereNull 與 whereNotNull** `whereNull`?方法驗證字段的值為?`NULL`： ~~~ $users = DB::table('users') ->whereNull('updated_at') ->get(); ~~~ `whereNotNull`?方法驗證字段的值?**不**?為?`NULL`： ~~~ $users = DB::table('users') ->whereNotNull('updated_at') ->get(); ~~~ **whereDate / whereMonth / whereDay / whereYear** `whereDate`?方法比較某字段的值與指定的日期是否相等： ~~~ $users = DB::table('users') ->whereDate('created_at', '2016-12-31') ->get(); ~~~ `whereMonth`?方法比較某字段的值是否與一年的某一個月份相等： ~~~ $users = DB::table('users') ->whereMonth('created_at', '12') ->get(); ~~~ `whereDay`?方法比較某列的值是否與一月中的某一天相等： ~~~ $users = DB::table('users') ->whereDay('created_at', '31') ->get(); ~~~ `whereYear`?方法比較某列的值是否與指定的年份相等： ~~~ $users = DB::table('users') ->whereYear('created_at', '2016') ->get(); ~~~ **whereColumn** `whereColumn`?方法用來檢測兩個列的數據是否一致： ~~~ $users = DB::table('users') ->whereColumn('first_name', 'last_name') ->get(); ~~~ 此方法還可以使用運算符： ~~~ $users = DB::table('users') ->whereColumn('updated_at', '>', 'created_at') ->get(); ~~~ `whereColumn`?方法可以接收數組參數。條件語句會使用?`and`?連接起來： ~~~ $users = DB::table('users') ->whereColumn([ ['first_name', '=', 'last_name'], ['updated_at', '>', 'created_at'] ])->get(); ~~~ ### 參數分組有時你可能需要創建更高級的 where 子句，例如「where exists」或者嵌套的參數分組。Laravel 的查詢構造器也能夠處理這些。讓我們先來看一個在括號中將約束分組的示例： ~~~ DB::table('users') ->where('name', '=', 'John') ->orWhere(function ($query) { $query->where('votes', '>', 100) ->where('title', '<>', 'Admin'); }) ->get(); ~~~ 如你所見，上面例子會傳遞一個?`閉包`?到?`orWhere`?方法，告訴查詢構造器開始一個約束分組。此?`閉包`?接收一個查詢構造器實例，你可用它來設置應包含在括號分組內的約束。這個例子會生成以下 SQL： ~~~ select * from users where name = 'John' or (votes > 100 and title <> 'Admin') ~~~ ### Where Exists 語法 `whereExists`?方法允許你編寫?`where exists`?SQL 子句。此方法會接收一個?`閉包`?參數，此閉包接收一個查詢語句構造器實例，讓你可以定義應放在「exists」SQL 子句中的查找： ~~~ 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 查詢語句 Laravel 也支持查詢 JSON 類型的字段。目前，本特性僅支持 MySQL 5.7+ 和 Postgres數據庫。可以使用?`->`?運算符來查詢 JSON 列數據： ~~~ $users = DB::table('users') ->where('options->language', 'en') ->get(); $users = DB::table('users') ->where('preferences->dining->meal', 'salad') ->get(); ~~~ ## Ordering, Grouping, Limit 及 Offset #### orderBy `orderBy`?方法允許你根據指定字段對查詢結果進行排序。`orderBy`?方法的第一個參數是你想要用來排序的字段，而第二個參數則控制排序的順序，可以為?`asc`?或?`desc`： ~~~ $users = DB::table('users') ->orderBy('name', 'desc') ->get(); ~~~ #### latest / oldest `latest`?和?`oldest`?方法允許你更容易的依據日期對查詢結果排序。默認查詢結果將依據?`created_at`?列。或者,你可以使用字段名稱排序： ~~~ $user = DB::table('users') ->latest() ->first(); ~~~ #### inRandomOrder `inRandomOrder`?方法可以將查詢結果隨機排序。例如，你可以使用這個方法獲取一個隨機用戶： ~~~ $randomUser = DB::table('users') ->inRandomOrder() ->first(); ~~~ #### groupBy / having / havingRaw `groupBy`?和?`having`?方法可用來對查詢結果進行分組。`having`?方法的用法和?`where`?方法類似： ~~~ $users = DB::table('users') ->groupBy('account_id') ->having('account_id', '>', 100) ->get(); ~~~ `havingRaw`?方法可以將一個原始的表達式設置為?`having`?子句的值。例如，我們能找出所有銷售額超過 2,500 元的部門： ~~~ $users = DB::table('orders') ->select('department', DB::raw('SUM(price) as total_sales')) ->groupBy('department') ->havingRaw('SUM(price) > 2500') ->get(); ~~~ #### skip / take 你可以使用?`skip`?和?`take`?方法來限制查詢結果數量或略過指定數量的查詢： ~~~ $users = DB::table('users')->skip(10)->take(5)->get(); ~~~ 或者，你也可以使用?`limit`?和?`offset`?方法： ~~~ $users = DB::table('users') ->offset(10) ->limit(5) ->get(); ~~~ ## 條件語句有時候，你希望某個值為 true 時才執行查詢。例如，如果在傳入請求中存在指定的輸入值的時候才執行這個?`where`?語句。你可以使用?`when`?方法實現： ~~~ $role = $request->input('role'); $users = DB::table('users') ->when($role, function ($query) use ($role) { return $query->where('role_id', $role); }) ->get(); ~~~ 只有當?`when`?方法的第一個參數為?`true`?時，閉包里的?`where`?語句才會執行。如果第一個參數是?`false`，這個閉包將不會被執行。你可能會把另一個閉包當作第三個參數傳遞給?`when`?方法。如果第一個參數的值為?`false`?時，這個閉包將執行。為了說明如何使用此功能，我們將使用它配置默認排序的查詢： ~~~ $sortBy = null; $users = DB::table('users') ->when($sortBy, function ($query) use ($sortBy) { return $query->orderBy($sortBy); }, function ($query) { return $query->orderBy('name'); }) ->get(); ~~~ ## Inserts 查詢構造器也提供了?`insert`?方法，用來插入記錄到數據表中。`insert`?方法接收一個包含字段名和值的數組作為參數： ~~~ DB::table('users')->insert( ['email' => 'john@example.com', 'votes' => 0] ); ~~~ 你甚至可以在?`insert`?調用中傳入一個嵌套數組向表中插入多條記錄。每個數組表示要插入表中的行： ~~~ DB::table('users')->insert([ ['email' => 'taylor@example.com', 'votes' => 0], ['email' => 'dayle@example.com', 'votes' => 0] ]); ~~~ #### 自增 ID 若數據表存在自增 id，則可以使用?`insertGetId`?方法來插入記錄并獲取其 ID： ~~~ $id = DB::table('users')->insertGetId( ['email' => 'john@example.com', 'votes' => 0] ); ~~~ > {note} 當使用 PostgreSQL 時，insertGetId 方法將預測自動遞增字段的名稱為?`id`。若你要從不同「順序」來獲取 ID，則可以將順序名稱作為第二個參數傳遞給?`insertGetId`?方法。 ## Updates 當然，除了在數據庫中插入記錄外，你也可以使用?`update`?來更新已存在的記錄。`update`?方法和?`insert`?方法一樣，接收含有字段及值的數組，其中包括要更新的字段。可以使用?`where`?子句來約束?`update`?查找： ~~~ DB::table('users') ->where('id', 1) ->update(['votes' => 1]); ~~~ ### Updating JSON Columns 當更新一個JSON 列時,你應該使用?`->`?語法來訪問 JSON 對象的鍵。僅在數據庫支持 JSON 列的時候才可使用這個操作： ~~~ DB::table('users') ->where('id', 1) ->update(['options->enabled' => true]); ~~~ ### 自增或自減查詢構造器也為指定字段提供了便利的自增和自減方法。此方法提供了一個比手動編寫?`update`?語法更具表達力且更精練的接口。這兩個方法都必須接收至少一個參數（要修改的字段）。也可選擇傳入第二個參數，用來控制字段應遞增／遞減的量： ~~~ 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']); ~~~ ## Deletes 查詢構造器也可使用?`delete`?方法從數據表中刪除記錄。在?`delete`?前，還可使用?`where`?子句來約束?`delete`語法： ~~~ DB::table('users')->delete(); DB::table('users')->where('votes', '>', 100)->delete(); ~~~ 如果你需要清空表，你可以使用?`truncate`?方法，這將刪除所有行，并重置自動遞增 ID 為零： ~~~ DB::table('users')->truncate(); ~~~ ## 悲觀鎖查詢構造器也包含一些可以幫助你在?`select`?語法上實現「悲觀鎖定」的函數。若要在查詢中使用「共享鎖」，可以使用?`sharedLock`?方法。共享鎖可防止選中的數據列被篡改，直到事務被提交為止： ~~~ DB::table('users')->where('votes', '>', 100)->sharedLock()->get(); ~~~ 另外，你也可以使用?`lockForUpdate`?方法。使用「更新」鎖可避免行被其它共享鎖修改或選取： ~~~ DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get(); ~~~