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 撰寫,請勿全文複製,轉載時請註明出處及連結,謝謝 😀

2 則留言

  1. 作者已經移除這則留言。

    回覆刪除
  2. 您好,首先謝謝您的系列教學讓我收穫很多!

    有些問題想請教一下,我目前使用最新版的laravel,安裝 VS Code、Mac OS

    Laravels內大部分的函數都可以透過cmd + 滑鼠右鍵連到原始檔看程式碼,唯獨DB操作相關的皆無法連到原始檔看程式碼? 我有試過使用 PHPStorm也有一樣的問題。

    舉例來說:
    DB::insert(.......); 我想查看DB和 insert的原始碼、函數的用途、要放哪些參數,完全看不到。

    XXModel::orderBy(......)->get(); 我想查看orderBy, get的原始碼和函數的用途、參數,也是都看不到。

    請問您有遇過這個問題嗎?一直google不到相關資訊

    謝謝

    回覆刪除

留言小提醒:
1.回覆時間通常在晚上,如果太忙可能要等幾天。
2.請先瀏覽一下其他人的留言,也許有人問過同樣的問題。
3.程式碼請先將它編碼後再貼上。(線上編碼:http://bit.ly/1DL6yog)
4.文字請加上標點符號及斷行,難以閱讀者恕難回覆。
5.感謝您的留言,您的問題也可能幫助到其他有相同問題的人。