Laravel 學習筆記(15) - 資料庫之 Query Builder

Query Builder

Laravel 提供了方便易用的資料庫查詢機制，基於 PDO 參數綁定(parameter binding)，保護應用程式免於 SQL資料隱碼 (SQL injection) 攻擊。

Selects(查詢)

取得整個資料表

$posts = DB::table('posts')->get();

foreach ($posts as $post) {
    var_dump($post->title);
}

foreach 中的 $post 代表一個資料列，$post->title 為這個列的名稱為 title 欄位的值。

取得單一資料列

$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()。get() 回傳的是 Builder 陣列。

where 語句

$posts = DB::table('posts')->where('id', '=', 1)->get();

where 有 3 個參數，(欄位名稱, 運算子, 值)，前面個參數有單引號包住，運算子可以用 '=', '<', '>', '>=', '<=', '<>' 等等，第 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'。等效 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 等，這種明確指定的方式來指定要比對的是哪個資料表的哪個欄位。

等效 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;

Inserts (新增)

DB::table('posts')->insert(
    ['title'=>'Hello!', 'content'=>'Laravel Demo~']
);

使用 insert() 方法，參數為陣列，key 表示欄位名稱，value 就是該欄位的值。

等效 SQL：

INSERT INTO posts (title, content) VALUES ('Hello!', 'Laravel Demo~');

如果資料表的 id 欄位是自動遞增，可以在新增資料的同時，取得該新增的 id：

$id = DB::table('posts')->insertGetId(
    ['title'=>'Hello 2!', 'content'=>'Laravel Demo~']
);

var_dump($id);

改用 insertGetId() 即可。

如果要一次新增多筆資料：

DB::table('posts')->insert([
    ['title'=>'Hello! 1', 'content'=>'Laravel Demo~'],
    ['title'=>'Hello! 2', 'content'=>'Laravel Demo~'],
    ['title'=>'Hello! 3', 'content'=>'Laravel Demo~']
]);

Updates (更新)

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;

Deletes (刪除)

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;

本文網址：http://blog.tonycube.com/2015/01/laravel-15-query-builder.html
由 Tony Blog 撰寫，請勿全文複製，轉載時請註明出處及連結，謝謝 😀