# Query Builder Laravel 提供 DB 類別來操作資料庫，基於 PDO 參數綁定(parameter binding)，保護應用程式免於 SQL資料隱碼 ([SQL injection](http://zh.wikipedia.org/wiki/SQL%E8%B3%87%E6%96%99%E9%9A%B1%E7%A2%BC%E6%94%BB%E6%93%8A)\[1]) 攻擊。本章將會介紹如何使用 Query Builder 來執行 CRUD。 ## 查詢 (Select) 查詢能讓你依需求讀取資料庫中的資料。 ### 取得整個資料表 ``` $posts = DB::table('posts')->get(); foreach ($posts as $post) { var_dump($post->title); } ``` 使用 DB 類別的 table() 方法，參數為資料表的名稱，可以取得整個資料表的內容。get() 方法會將資料以陣列的方式回傳。 foreach 中的 $post 代表一個資料列，$post->title 會取得 title 這個欄位的值。 註：var\_dump() 是 PHP 的函式，用來顯示變數的內容。 ### 取得單一資料列 ``` $post = DB::table('posts')->where('id', '=', 1)->first(); var_dump($post->title); ``` 利用 where 來找到 id 為 1 的資料列，雖然你預期只會有一筆資料，但 where 回傳的是集合，所以最後要用 first() 只取第一列資料。 ### 取得單一資料列中的單一欄位 ``` $title = DB::table('posts')->where('id', '=', 1)->pluck('title'); var_dump($title); ``` pluck 英文為摘取的意思，這裡就是從資料列中摘取 title 這個欄位的值。查看原始碼，pluck 已經做了 first() 的動作，所以在取得該欄位的值之前，已經先取得第一列的資料。 ### 取得多資料列中的單一欄位 ``` $titles = DB::table('posts')->lists('title'); var_dump($titles); ``` 前一個例子只有取回某一列的某一欄位的值，如果要取回所有資料列中的某一欄位的值，可以用 lists()，它會回傳陣列。 ### 查詢子句 ``` $posts = DB::table('posts')->select('id', 'title')->get(); $posts = DB::table('posts')->distinct()->get(); $posts = DB::table('posts')->select('title as subject')->get(); ``` table() 方法會取得整個資料表的內容，如果在後面串上查詢子句，就可以做進一步的篩選。例如第一行，指定只要取回 id 及 title 兩個欄位。 第二行的 distinct() 會排除重覆的內容。 第三行，利用 as 可以設定別名。這時候回傳的結果中，原本的欄位名稱 title 就被換成 subject 了。 ### 在查詢結果中，再加入查詢 ``` $query = DB::table('posts')->select('title'); $posts = $query->addSelect('content')->get(); ``` 將原本的查詢拆成兩個動作。注意！第一行沒有使用 get()，所以回傳的是 Builder 物件($query)，這樣第二行才能串 addSelect()。你可以重覆使用這個 $qurey 去篩選不同的資料。 ### where 語句 ``` $posts = DB::table('posts')->where('id', '=', 1)->get(); ``` where 有 3 個參數，(欄位名稱, 運算子, 值)，前面 2 個參數有單引號包住，運算子可以用 '=', '<', '>', '>=', '<=', '<>' 等等，第 3 個參數是要比對的值，要注意的是，數值不加單引號，字串、日期等則要加。 如果第 2 個參數是 '=' (等於)的話，可以省略，寫成 where('id', 1) ### or 多個條件： ``` $posts = DB::table('posts')->where('id', '=', 1) ->orWhere('title', '111') ->get(); ``` 等效 SQL： ``` SELECT * FROM posts WHERE (id = 1) or (title = '111'); ``` ### between 範圍： ``` $posts = DB::table('posts')->whereBetween('id', [2,4])->get(); ``` 等效 SQL： ``` SELECT * FROM posts WHERE id BETWEEN 2 AND 4; ``` 如果要相反呢？範圍內的都不要： ``` $posts = DB::table('posts')->whereNotBetween('id', [2,4])->get(); ``` 等效 SQL： ``` SELECT * FROM posts WHERE id NOT BETWEEN 2 AND 4; ``` ### In 某個(或多個)值 ``` $posts = DB::table('posts')->whereIn('id', [1,3,5])->get(); ``` 這樣只會取 id 是 1, 3, 5 的資料列。 等效 SQL： ``` SELECT * FROM posts WHERE id IN (1,3,5); ``` 當然也可以相反啦： ``` $posts = DB::table('posts')->whereNotIn('id', [1,3,5])->get(); ``` 這樣就變成只有 1, 3, 5 不要，其他全要了。 等效 SQL： ``` SELECT * FROM posts WHERE id NOT IN (1,3,5); ``` ### Order By 排序方式 ``` $posts = DB::table('posts')->orderBy('id', 'desc')->get(); ``` 這樣會以 id 的值，從大到小(descending)排列；若要從小到大(ascending)就改為 'asc'。預設為 asc，可省略。 等效 SQL： ``` SELECT * FROM posts ORDER BY id DESC; ``` ### Group By 群組及 Having 條件 ``` $posts = DB::table('posts')->groupBy('tag')->having('words','>', 100)->get(); 假設每篇文章都有一個 tag，我們可以將相同名稱的 tag 組成一組。然後在分組的結果上，把字數(這裡假設 words 儲存文章的字數)大於 100 的群組(tag)取出來。 ``` having 功能和 where 相同，只是 having 可以用在運算結果(這裡指群組化，有時候可能會是加總之類的)之後，而 where 不能。 等效 SQL： ``` SELECT * FROM posts GROUP BY tag HAVING words > 100; ``` ### Offset & Limit 指標位移及限制 ``` $posts = DB::table('posts')->skip(5)->take(3)->get(); ``` skip(5)表示，前面 5 筆資料不要，從第 6 筆開始取，而且 take(3) 只取 3 筆。也就是第 6, 7, 8 筆。 你也可以指定只取一筆： ``` $posts = DB::table('posts')->take(1)->get(); ``` ### Joins 結合資料表 #### 連接 假設我們有一個資料表 comments，儲存讀者的回應，它有一個欄位 post\_id 用來儲存對應的文章 id。 現在，我們可以結合這兩張表： ``` $posts = DB::table('posts') ->join('comments', 'posts.id', '=', 'comments.post_id') ->select('posts.id', 'posts.title', 'comments.content') ->get(); ``` join() 方法的第 1 個參數表示要結合的另一個表格的名稱；第 2 到 4 個參數，則是這兩個表格要比較的欄位。 利用 posts.id、comments.post\_id 等，這種明確指定的方式來指定要比對的是哪個資料表的哪個欄位。最後用 select() 明確指定要取回的欄位名稱。 等效 SQL： ``` SELECT posts.id, posts.title, comments.content FROM posts, comments WHERE posts.id = comments.post_id; ``` 連接只會取得條件完全符合的資料列，假如有文章沒有回應，就不會取出。那如果我們要顯示所有文章，而如果該文章有回應的話就顯示，沒有也沒關係，可以用 Left Join。 #### LEFT JOIN 左外部連接 ``` $posts = DB::table('posts') ->leftJoin('comments', 'posts.id', '=', 'comments.post_id') ->select('posts.id', 'posts.title', 'comments.content') ->get(); ``` 把原本的 join() 換成 leftJoin()。 等效 SQL： ``` SELECT posts.id, posts.title, comments.content FROM posts LEFT JOIN comments ON posts.id = comments.post_id; ``` 這裡的左資料表就是 posts，結果會以它為主來取出所有的資料列，這裡 select 的 comments.content 欄位，如果有資料就會取出，如果它沒有則以 NULL 表示。 ### 進階 Where 條件 ``` $posts = DB::table('posts') ->where('title', '=', '111') ->orWhere(function($query) { $query->where('words', '>', 100) ->where('tag', '=', 'php'); }) ->get(); ``` 串接多個 where() 等同於 and 條件，使用 orWhere 等同於 or 條件。在條件中可以嵌套其他條件。 等效 SQL： ``` SELECT * FROM posts WHERE title = '111' or (words > 100 and tag = 'php'); ``` ### Aggregates 集合方法(函數) 這裡列出使用方式及等效 SQL： ``` //計算資料筆(列)數 $postCount = DB::table('posts')->count(); //SELECT count(*) FROM posts; //取 words 欄位最大值 $maxWords = DB::table('posts')->max('words'); //SELECT max(words) FROM posts; //取 words 欄位最小值 $minWords = DB::table('posts')->min('words'); //SELECT min(words) FROM posts; //計算 words 欄位平均值 $avgWords = DB::table('posts')->avg('words'); //SELECT avg(words) FROM posts; //計算 words 欄位總計值 $sumWords = DB::table('posts')->sum('words'); //SELECT sum(words) FROM posts; ``` ### Raw Expressions 原生表達式 有時候你會需要使用原生 SQL 來查詢，可以使用 DB::raw 方法達成。 要注意的是，你所撰寫的原生 SQL 是一段字串，因此必須小心 SQL 隱碼(SQL injection) 攻擊。 ``` $posts = DB::table('posts') ->select(DB::raw('count(*) as post_count')) ->get(); ``` 等效 SQL： ``` SELECT count(*) as post_count FROM posts; ``` ## 新增 (Insert) ``` DB::table('posts')->insert( ['title'=>'Hello!', 'content'=>'Laravel Demo~'] ); ``` 使用 insert() 方法，參數為陣列，key 表示欄位名稱，value 就是該欄位的值。 等效 SQL： ``` INSERT INTO posts (title, content) VALUES ('Hello!', 'Laravel Demo~'); ``` 如果資料表的 id 欄位是自動遞增，可以在新增資料的同時，取得該新增的 id，改用 insertGetId() 即可： ``` $id = DB::table('posts')->insertGetId( ['title'=>'Hello 2!', 'content'=>'Laravel Demo~'] ); var_dump($id); ``` 如果要一次新增多筆資料： ``` DB::table('posts')->insert([ ['title'=>'Hello! 1', 'content'=>'Laravel Demo~'], ['title'=>'Hello! 2', 'content'=>'Laravel Demo~'], ['title'=>'Hello! 3', 'content'=>'Laravel Demo~'] ]); ``` ## 更新 (Update) ``` DB::table('posts') ->where('id', 1) ->update(['title'=>'Hi!']); ``` 等效 SQL： ``` UPDATE posts SET title = 'Hi!' WHERE id = 1; ``` 如果要對整個資料表中某個**\[數值]**欄位做遞增或遞減的動作： ``` DB::table('posts')->increment('words'); DB::table('posts')->increment('words', 10); DB::table('posts')->decrement('words'); DB::table('posts')->decrement('words', 10); ``` 這樣全部的 words 欄位都會受影響。increment 會 +1，如果有第 2 個參數則為以這個數字做累加，例如這裡的 +10；decrement 則是遞減。 等效 SQL： ``` UPDATE posts SET words = words + 1; UPDATE posts SET words = words + 10; UPDATE posts SET words = words - 1; UPDATE posts SET words = words - 10; ``` 只要加入 where 就可以指定某一列資料做累加： ``` DB::table('posts') ->where('id',1) ->increment('words', 1); ``` 等效 SQL： ``` UPDATE posts SET words = words + 1 WHERE id = 1; ``` ## 刪除 (Delete) ``` DB::table('posts') ->where('words', '>', 200) ->delete(); ``` 等效 SQL： ``` DELETE FROM posts WHERE words > 200; ``` 記得要加 where 條件，不然整個資料表的資料就掰了，像這樣： ``` DB::table('posts')->delete(); ``` 等效 SQL： ``` DELETE FROM posts; ``` 這樣就是刪除整個資料表的資料。 也可以使用清除整個資料表的方法： ``` DB::table('posts')->truncate(); ``` 等效 SQL： ``` TRUNCATE TABLE posts; ``` * \[1]