Query Builder

Laravel 提供 DB 類別來操作資料庫,基於 PDO 參數綁定(parameter binding),保護應用程式免於 SQL資料隱碼 (SQL injection[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;