查詢構建器的使用 · Laravel學習筆記

## **簡介** Laravel 數據庫功能的核心就是提供流式接口與數據庫進行交互的查詢構建器（Query Builder），支持 MySQL、Postgres、SQLite 和 SQL Server 等常見的數據庫管理系統。 > 注：關于流式接口可以查看[流接口模式](https://xueyuanjun.com/post/2828.html)了解明細。 ## **使用 DB 門面執行原生 SQL 語句** 我們可以直接通過`DB`門面提供的方法執行原生的 SQL 語句（注意安全問題），`DB`門面既可以用于構建查詢構建器方法鏈，也可以用于原生語句的執行。 ~~~ //返回包含所有查詢結果的`stdClass`對象數組 $users = DB::select('select * from `users`'); //當指定查詢條件時，可借助PDO的參數綁定來防范SQL注入 $name = '學院君'; $users = DB::select('select * from `users` where `name` = ?', [$name]); $name = str_random(10); $email = str_random(10) . '@163.com'; $password = bcrypt('secret'); //插入成功，返回`true`，插入失敗，則拋出`QueryException`異常 $flag = DB::insert('insert into `users` (`name`, `email`, `password`) values (?, ?, ?)', [$name, $email, $password]); $name = str_random(8); $id = 8; //更新成功，返回受影響行數，更新出錯，則拋出`QueryException`異常 $affectedRows = DB::update('update `users` set `name` = ? where id = ?', [$name, $id]); $id = 8; //刪除成功，返回受影響行數，更新出錯，則拋出`QueryException`異常 $affectedRows = DB::delete('delete from `users` where id = ?', [$id]); ~~~ ## **使用查詢構建器進行增刪改查** Laravel 數據庫功能的核心組件 —— 查詢構建器（說是核心，是因為 Eloquent 模型的底層也是基于這個查詢構建器），是我們與數據庫的交互的基礎（需要直接或間接通過它來完成）。查詢構建器也是基于`DB`門面的，只不過需要調用其提供的`table`方法構建一個基于指定數據表的查詢構建器。下面我們就通過查詢構建器來依次實現上面通過`DB`門面執行原生 SQL 語句完成的增刪改查功能。 ~~~ $users = DB::table('users')->get(); //使用查詢構建器進行查詢，無需手動設置參數綁定來規避 SQL 注入攻擊，因為 Laravel 底層會幫助我們自動實現參數綁定 $name = '學院君'; $users = DB::table('users')->where('name', $name)->get(); //默認返回所有字段，要指定查詢的字段，可以通過`select`方法來實現 $user = DB::table('users')->select('id', 'name', 'email')->where('name', $name)->first(); $flag = DB::table('users')->insert([ 'name' => str_random(10), 'email' => str_random(8) . '@163.com', 'password' => bcrypt('secret') ]); //如果想要在插入之后獲取對應記錄的主鍵 ID，將`insert`方法改為調用`insertGetId`方法 $id = 11; $affectedRows = DB::table('users')->where('id', '>', $id)->update(['name' => str_random(8)]); $id = 11; $affectedRows = DB::table('users')->where('id', '>=', $id)->delete(); ~~~ ## **查詢小技巧** 我們先來介紹幾個 Laravel 自帶的語法糖，可以幫助我們快速獲取期望的查詢結果，提高編碼效率： - 獲取指定字段的值，而不是一行或幾行記錄： ~~~ $name = '學院君'; $email = DB::table('users')->where('name', $name)->value('email'); //返回指定字段的值，無需做額外的判斷和提取操作 ~~~ - 判斷某個字段值在數據庫中是否存在對應記錄，可以通過`exists`方法快速實現： ~~~ $exists = DB::table('users')->where('name', $name)->exists(); //如果存在，返回`true`，否則返回`false`。該方法還有一個與之相對的方法`doesntExist()` ~~~ - 獲取查詢結果以某個字段值為值構建關聯數組，可通過調用`pluck`方法快速實現： ~~~ $users = DB::table('users')->where('id', '<', 10)->pluck('name', 'id'); //在傳遞參數到`pluck`方法的時候，鍵對應的字段在后面，值對應的字段在前面 ~~~ - 當數據庫返回結果集較大時，可以借助`chunk`方法將其分割成多個的組塊依次返回進行處理： ~~~ $names = []; DB::table('users')->orderBy('id')->chunk(5, function ($users) use (&$names) { foreach ($users as $user) { $names[] = $user->name; } }); //將獲取的結果集每次返回5個進行處理——用戶名依次放到`$names`數組中 ~~~ ## **一些復雜的查詢語句** 在日常開發中，往往會涉及到一些較復雜的查詢語句，比如連接查詢、子查詢、排序、分頁、聚合查詢等等，這里我們將圍繞這些內容展開探討。 ### **聚合函數** 在開發后臺管理系統時，經常需要對數據進行統計、求和、計算平均值、最小值、最大值等，對應的方法名分別是`count`、`sum`、`avg`、`min`、`max`： ~~~ $num = DB::table('users')->count(); # 計數 9 $sum = DB::table('users')->sum('id'); # 求和 45 $avg = DB::table('users')->avg('id'); # 平均值 5 $min = DB::table('users')->min('id'); # 最小值 1 $max = DB::table('users')->max('id'); # 最大值 9 ~~~ ### **高級 Where 查詢** - like查詢 ~~~ DB::table('posts')->where('title', 'like', 'Laravel學院%')->get(); ~~~ - and查詢 ~~~ DB::table('posts')->where('id', '<', 10)->where('views', '>', 0)->get(); //亦可以通過傳入數組參數的方式實現上述代碼同樣的功能 DB::table('posts')->where([ ['id', '<', 10], ['views', '>', 0] ])->get(); ~~~ - or查詢 ~~~ DB::table('posts')->where('id', '<', 10)->orWhere('views', '>', 0)->get(); //多個and查詢可以通過多個where方法連接，同理多個or查詢也可以通過多個orWhere方法連接 ~~~ - between查詢 ~~~ DB::table('posts')->whereBetween('views', [10, 100])->get(); //與之相對的還有一個whereNotBeween方法 ~~~ - in查詢 ~~~ DB::table('posts')->whereIn('user_id', [1, 3, 5, 7, 9])->get(); //使用該方法時第二個參數不能是空數組，與之相對的還有一個whereNotIn方法 ~~~ - null查詢 ~~~ DB::table('users')->whereNull('email_verified_at')->get(); //與之相對的還有一個whereNotNull方法 ~~~ - 字段本身之間比較查詢 ~~~ DB::table('posts')->whereColumn('updated_at', '>', 'created_at')->get(); //where updated_at > created_at ~~~ - JSON查詢從 MySQL 5.7 開始，數據庫字段原生支持 JSON 類型，對于 JSON 字段的查詢，和普通 where 查詢并無區別，只是支持對指定 JSON 屬性的查詢： ~~~ DB::table('users') ->where('options->language', 'en') ->get(); //如果屬性字段是個數組，還支持通過`whereJsonContains`方法對數組進行包含查詢 ~~~ - 參數分組，考慮下面這個 SQL 語句： ~~~ select * from posts where id <= 10 or (views > 0 and created_at < '2018-11-28 14:00'); ~~~ 貌似我們通過前面學到的方法解決不了這個查詢語句的構造，所以我們需要引入更復雜的構建方式，那就是引入匿名函數的方式（和連接查詢中構建復雜的連接條件類似）： ~~~ DB::table('posts')->where('id', '<=', 10)->orWhere(function ($query) { $query->where('views', '>', 0) ->whereDate('created_at', '<', '2018-11-28') ->whereTime('created_at', '<', '14:00'); })->get(); ~~~ 在這個匿名函數中傳入的`$query`變量也是一個查詢構建器的實例。這一查詢構建方式叫做「參數分組」，在帶括號的復雜 WHERE 查詢子句中都可以參考這種方式來構建查詢語句。 ### **連接查詢** 在查詢構建器中我們通過`join`方法來實現內連接（包含等值連接和不等連接），通過`leftJoin`方法實現左(外)連接，通過`rightJoin`方法實現右(外)連接，舉個例子： ~~~ $posts = DB::table('posts') ->join('users', 'users.id', '=', 'posts.user_id') ->select('posts.*', 'users.name', 'users.email') ->get(); //對應的SQL語句為：select posts.*, users.name, users.email from posts inner join users on users.id = posts.user_id; ~~~ 有時候，你的連接查詢條件可能比較復雜，比如下面這種： ~~~ select posts.*, users.name, users.email from posts inner join users on users.id = posts.user_id and users.email_verified_at is not null where posts.views > 0; ~~~ 這個時候，我們可以通過匿名函數來組裝連接查詢的條件來構建上面的查詢語句： ~~~ $posts = DB::table('posts') ->join('users', function ($join) { $join->on('users.id', '=', 'posts.user_id') ->whereNotNull('users.email_verified_at'); }) ->select('posts.*', 'users.name', 'users.email') ->where('posts.views', '>', 0) ->get(); ~~~ 我們可以在匿名函數的`$join`實例上調用所有 Where 查詢子句，以組裝我們需要的連接查詢條件。 ### **聯合查詢** 查詢構建器還支持通過`union`方法合并多個查詢結果： ~~~ $posts_a = DB::table('posts')->where('views', 0); $posts_b = DB::table('posts')->where('id', '<=', 10)->union($posts_a)->get(); 對應的SQL語句為： (select * from `posts` where `id` <= 10) union (select * from `posts` where `views` = 0) ~~~ 通過上面這段代碼，我們將`views = 0`和`id <= 10`這兩個查詢結果合并到了一起。 ### **分組** 查詢構建器還提供了`groupBy`方法用于對結果集進行分組： ~~~ $posts = DB::table('posts') ->groupBy('user_id') ->selectRaw('user_id, sum(views) as total_views') ->get(); //對應的SQL語句為：select user_id, sum(views) as total_views from `posts` group by `user_id`; ~~~ 如果我們想要進一步對分組結果進行過濾，可以使用`having`方法，比如，要從上述分組結果中過濾出總瀏覽數大于等于`10`的記錄，可以這么做： ~~~ $posts = DB::table('posts') ->groupBy('user_id') ->selectRaw('user_id, sum(views) as total_views') ->having('total_views', '>=', 10) ->get(); //對應的SQL語句為：select user_id, sum(views) as total_views from `posts` group by `user_id` having `total_views` >= 10; ~~~ ### **分頁** 在日常開發中，最常見的查詢場景就是分頁查詢了，在查詢構建器中提供了兩種方式來進行分頁查詢： - 通過`skip`方法和`take`方法組合進行分頁，`skip`方法傳入的參數表示從第幾條記錄開始，`take`傳入的參數表示一次獲取多少條記錄： ~~~ $posts = DB::table('posts')->orderBy('created_at', 'desc') ->where('views', '>', 0) ->skip(10)->take(5) ->get(); //對應的SQL語句為：select * from `posts` where `views` > 0 order by `created_at` desc limit 5 offset 10; ~~~ - 通過`offset`方法和`limit`方法組合進行分頁查詢，`offset`表示從第幾條記錄開始，`limit`表示一次獲取多少條記錄，使用方式和`skip`和`take`類似： ~~~ $posts = DB::table('posts')->orderBy('created_at', 'desc') ->where('views', '>', 0) ->offset(10)->limit(5) ->get(); //對應的SQL語句為： select * from `posts` where `views` > 0 order by `created_at` desc limit 5 offset 10; ~~~