資料庫：Query Builder

• 簡介

• 執行資料庫查詢

  • 將結果分段
  • 延遲的查詢結果資料流
  • 彙總

• Select 陳述式

• 原始陳述式

• Join

• Union

• 基礎的 Where 子句

  • Where 子句
  • Or Where 子句
  • JSON Where 子句
  • 其他 Where 子句
  • 邏輯群組

• 進階 Where 子句

  • Where Exists 子句
  • Subquery Where 子句

• Order、Group、Limit、Offset

  • Order
  • Group
  • Limit 與 Offset

• 條件式子句

• Insert 陳述式

  • Upsert

• Update 陳述式

  • 更新 JSON 欄位
  • 遞增與遞減

• Delete 陳述式

• 悲觀鎖定

• 偵錯

簡介

Laravel 的資料庫 Query Builder 提供了方便流暢的介面，可用於建立與執行資料庫查詢。Laravel 的 Query Builder 可以在專案中進行各類資料庫動作，且可以在所有 Laravel 支援的資料庫系統上使用。

Laravel 的 Query Builder 使用 PDO 參數繫結來保護網站免於 SQL 注入攻擊。在將字串作為查詢繫結傳入 Query Builder 時，不需要清理或消毒字串。

執行資料庫查詢

從資料表中取得所有欄位

我們可以使用 DB Facade 的 table 方法來進行查詢。table 方法會回傳用於給定資料表的 Fluent Query Builder 實體。使用該實體，我們就能在查詢上串接更多的查詢條件，並在最後使用 get 方法來取得查詢的結果：

 1<?php
 2 
 3namespace App\Http\Controllers;
 4 
 5use App\Http\Controllers\Controller;
 6use Illuminate\Support\Facades\DB;
 7 
 8class UserController extends Controller
 9{
10    /**
11     * Show a list of all of the application's users.
12     *
13     * @return \Illuminate\Http\Response
14     */
15    public function index()
16    {
17        $users = DB::table('users')->get();
18 
19        return view('user.index', ['users' => $users]);
20    }
21}
 1<?php
 2 
 3namespace App\Http\Controllers;
 4 
 5use App\Http\Controllers\Controller;
 6use Illuminate\Support\Facades\DB;
 7 
 8class UserController extends Controller
 9{
10    /**
11     * Show a list of all of the application's users.
12     *
13     * @return \Illuminate\Http\Response
14     */
15    public function index()
16    {
17        $users = DB::table('users')->get();
18 
19        return view('user.index', ['users' => $users]);
20    }
21}

Illuminate\Support\Collection 實體的 get 方法會以 PHP stdClass 物件來回傳查詢的結果。要存取各個欄位的值時，我們可以把該物件上的屬性當作欄位來存取：

1use Illuminate\Support\Facades\DB;
2 
3$users = DB::table('users')->get();
4 
5foreach ($users as $user) {
6    echo $user->name;
7}
1use Illuminate\Support\Facades\DB;
2 
3$users = DB::table('users')->get();
4 
5foreach ($users as $user) {
6    echo $user->name;
7}

從資料表中取得單行或單一欄位

若只需要從資料庫資料表中取得單一行的話，可使用 DB Facade 的 first 方法。該方法只會回傳一個 stdClass 物件：

1$user = DB::table('users')->where('name', 'John')->first();
2 
3return $user->email;
1$user = DB::table('users')->where('name', 'John')->first();
2 
3return $user->email;

若不需要整行的資料庫，可以使用 value 方法來從一筆記錄中取得單一值。該方法會直接回傳欄位的值：

1$email = DB::table('users')->where('name', 'John')->value('email');
1$email = DB::table('users')->where('name', 'John')->value('email');

若要使用 id 欄位的值來取得某一行，可使用 find 方法：

1$user = DB::table('users')->find(3);
1$user = DB::table('users')->find(3);

取得一組欄位值的清單

若想將某個欄位的所有值放在 Illuminate\Support\Collection 實體中取得，可使用 pluck 方法。在這個範例中，我們會取得一個包含使用者抬頭的 Collection：

1use Illuminate\Support\Facades\DB;
2 
3$titles = DB::table('users')->pluck('title');
4 
5foreach ($titles as $title) {
6    echo $title;
7}
1use Illuminate\Support\Facades\DB;
2 
3$titles = DB::table('users')->pluck('title');
4 
5foreach ($titles as $title) {
6    echo $title;
7}

我們也可以提供第二個引數給 pluck 來指定要在產生的 Collection 中使用哪個欄位來當作索引鍵：

1$titles = DB::table('users')->pluck('title', 'name');
2 
3foreach ($titles as $name => $title) {
4    echo $title;
5}
1$titles = DB::table('users')->pluck('title', 'name');
2 
3foreach ($titles as $name => $title) {
4    echo $title;
5}

將查詢結果分段

若要處理上千筆資料，請考慮使用 DB Facade 的 chunk 方法。該方法一次只會取得一小段資料，並將各個分段的內容傳入閉包中供我們處理。舉例來說，我們來以一次只取 100 筆記錄的方式分段取得整個 users 資料表：

1use Illuminate\Support\Facades\DB;
2 
3DB::table('users')->orderBy('id')->chunk(100, function ($users) {
4    foreach ($users as $user) {
5        //
6    }
7});
1use Illuminate\Support\Facades\DB;
2 
3DB::table('users')->orderBy('id')->chunk(100, function ($users) {
4    foreach ($users as $user) {
5        //
6    }
7});

我們也可以在閉包中回傳 false 來停止處理接下來的分段：

1DB::table('users')->orderBy('id')->chunk(100, function ($users) {
2    // Process the records...
3 
4    return false;
5});
1DB::table('users')->orderBy('id')->chunk(100, function ($users) {
2    // Process the records...
3 
4    return false;
5});

若要在為結果分段的同時更新資料庫中的資料，則分段的結果可能會不如預期。若要在為查詢結果分段的同時更新所取得的資料，最好該用 chunkById 方法。該方法會自動使用資料的主索引鍵來將結果分頁：

1DB::table('users')->where('active', false)
2    ->chunkById(100, function ($users) {
3        foreach ($users as $user) {
4            DB::table('users')
5                ->where('id', $user->id)
6                ->update(['active' => true]);
7        }
8    });
1DB::table('users')->where('active', false)
2    ->chunkById(100, function ($users) {
3        foreach ($users as $user) {
4            DB::table('users')
5                ->where('id', $user->id)
6                ->update(['active' => true]);
7        }
8    });

延遲的查詢結果資料流

lazy 方法與 chunk 方法 的原理類似，都是以分段的方式執行查詢。不過，lazy() 方法不是直接把每個分段傳入回呼中，而是回傳一個 LazyCollection。使用這個 LazyCollection，就可以以單一資料流的方式使用查詢結果：

1use Illuminate\Support\Facades\DB;
2 
3DB::table('users')->orderBy('id')->lazy()->each(function ($user) {
4    //
5});
1use Illuminate\Support\Facades\DB;
2 
3DB::table('users')->orderBy('id')->lazy()->each(function ($user) {
4    //
5});

一樣，若要在迭代查詢結果的同時更新這些資料的話，最好該用 lazyById 或 lazyByIdDesc 方法。這些方法會自動使用這些資料的主索引鍵來為查詢結果分頁：

1DB::table('users')->where('active', false)
2    ->lazyById()->each(function ($user) {
3        DB::table('users')
4            ->where('id', $user->id)
5            ->update(['active' => true]);
6    });
1DB::table('users')->where('active', false)
2    ->lazyById()->each(function ($user) {
3        DB::table('users')
4            ->where('id', $user->id)
5            ->update(['active' => true]);
6    });

彙總

Query Builder 還提供了許多用來取得彙總值的方法，如 count、max、min、avg、sum 等。我們可以在建立查詢時使用這些方法：

1use Illuminate\Support\Facades\DB;
2 
3$users = DB::table('users')->count();
4 
5$price = DB::table('orders')->max('price');
1use Illuminate\Support\Facades\DB;
2 
3$users = DB::table('users')->count();
4 
5$price = DB::table('orders')->max('price');

當然，我們也可以使用其他閉包來組合使用這些方法，以微調這些彙總值的計算方法：

1$price = DB::table('orders')
2                ->where('finalized', 1)
3                ->avg('price');
1$price = DB::table('orders')
2                ->where('finalized', 1)
3                ->avg('price');

判斷資料是否存在

我們不需要使用 count 來判斷是否有某個符合查詢條件的資料存在，而可以使用 exists 與 doesntExist 方法：

1if (DB::table('orders')->where('finalized', 1)->exists()) {
2    // ...
3}
4 
5if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
6    // ...
7}
1if (DB::table('orders')->where('finalized', 1)->exists()) {
2    // ...
3}
4 
5if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
6    // ...
7}

Select 陳述式

指定 Select 子句

我們不是每次都想把資料表上所有的欄位都抓下來。使用 select 方法，就可以指定查詢的「select」子句：

1use Illuminate\Support\Facades\DB;
2 
3$users = DB::table('users')
4            ->select('name', 'email as user_email')
5            ->get();
1use Illuminate\Support\Facades\DB;
2 
3$users = DB::table('users')
4            ->select('name', 'email as user_email')
5            ->get();

可使用 distinct 方法來強制查詢只回傳不重複的結果：

1$users = DB::table('users')->distinct()->get();
1$users = DB::table('users')->distinct()->get();

若已經有現有的 Query Builder 實體，而想在現有的 select 子句內新增欄位的話，可使用 addSelect 方法：

1$query = DB::table('users')->select('name');
2 
3$users = $query->addSelect('age')->get();
1$query = DB::table('users')->select('name');
2 
3$users = $query->addSelect('age')->get();

Raw 運算式

有時候，我們會需要在查詢中插入任意字串。若要建立 Raw(原始的) 字串運算式，可使用 DB Facade 提供的 raw 方法：

1$users = DB::table('users')
2             ->select(DB::raw('count(*) as user_count, status'))
3             ->where('status', '<>', 1)
4             ->groupBy('status')
5             ->get();
1$users = DB::table('users')
2             ->select(DB::raw('count(*) as user_count, status'))
3             ->where('status', '<>', 1)
4             ->groupBy('status')
5             ->get();

Raw 方法

除了使用 DB::raw 方法外，也可以使用下列方法將 Raw 陳述式插入到查詢中的各個部分。請記得，Laravel 無法保使用 Raw 運算式的查詢有受到避免 SQL Injection 弱點的保護。

selectRaw

可使用 selectRaw 來代替使用 addSelect(DB::raw(...))。該方法接受一個可選的第二引數，為一繫結陣列：

1$orders = DB::table('orders')
2                ->selectRaw('price * ? as price_with_tax', [1.0825])
3                ->get();
1$orders = DB::table('orders')
2                ->selectRaw('price * ? as price_with_tax', [1.0825])
3                ->get();

whereRaw / orWhereRaw

whereRaw 與 orWhereRaw 方法可用來在查詢中插入 Raw 的「where」子句。這兩個方法的第三個引數為一可選的繫結陣列：

1$orders = DB::table('orders')
2                ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
3                ->get();
1$orders = DB::table('orders')
2                ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
3                ->get();

havingRaw / orHavingRaw

havingRaw 與 orHavingRaw 方法可用來向「having」子句提供 Raw 字串作為該子句的值。這兩個方法的第三個引數為一可選的繫結陣列：

1$orders = DB::table('orders')
2                ->select('department', DB::raw('SUM(price) as total_sales'))
3                ->groupBy('department')
4                ->havingRaw('SUM(price) > ?', [2500])
5                ->get();
1$orders = DB::table('orders')
2                ->select('department', DB::raw('SUM(price) as total_sales'))
3                ->groupBy('department')
4                ->havingRaw('SUM(price) > ?', [2500])
5                ->get();

orderByRaw

orderByRaw 方法可用來提供「order by」子句原始字串作為該子句的值：

1$orders = DB::table('orders')
2                ->orderByRaw('updated_at - created_at DESC')
3                ->get();
1$orders = DB::table('orders')
2                ->orderByRaw('updated_at - created_at DESC')
3                ->get();

groupByRaw

groupByRaw 方法可用來提供「group by」子句原始字串作為該子句的值：

1$orders = DB::table('orders')
2                ->select('city', 'state')
3                ->groupByRaw('city, state')
4                ->get();
1$orders = DB::table('orders')
2                ->select('city', 'state')
3                ->groupByRaw('city, state')
4                ->get();

Join

Inner Join 子句

Query Builder 也可用來在查詢內加入 Join 子句。若要使用基本的「Inner Join」，可使用 Query Builder 實體上的 join 方法。傳給 join 方法的第一個引數是要 Join 的表名，而剩下的引數則為 Join 的欄位條件限制。在單一查詢上可以 Join 多張表：

1use Illuminate\Support\Facades\DB;
2 
3$users = DB::table('users')
4            ->join('contacts', 'users.id', '=', 'contacts.user_id')
5            ->join('orders', 'users.id', '=', 'orders.user_id')
6            ->select('users.*', 'contacts.phone', 'orders.price')
7            ->get();
1use Illuminate\Support\Facades\DB;
2 
3$users = DB::table('users')
4            ->join('contacts', 'users.id', '=', 'contacts.user_id')
5            ->join('orders', 'users.id', '=', 'orders.user_id')
6            ->select('users.*', 'contacts.phone', 'orders.price')
7            ->get();

Left Join 與 Right Join 子句

若不想新增「Innert Join」，而是想新增「Left Join」或「Right Join」，可使用 leftJoin 或 rightJoin。這些方法的簽章(Signature)與 join 方法相同：

1$users = DB::table('users')
2            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
3            ->get();
4 
5$users = DB::table('users')
6            ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
7            ->get();
1$users = DB::table('users')
2            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
3            ->get();
4 
5$users = DB::table('users')
6            ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
7            ->get();

Cross Join 子句

可使用 crossJoin 方法來新增「Cross Join」。Cross Join 會產生第一個表與被 Join 表的笛卡爾乘積(Cartesian Product)：

1$sizes = DB::table('sizes')
2            ->crossJoin('colors')
3            ->get();
1$sizes = DB::table('sizes')
2            ->crossJoin('colors')
3            ->get();

進階的 Join 子句

我們也可以指定更進階的 Join 子句。若要指定更進階的 Join 子句，join 方法的第二個引數請傳入一閉包。該閉包會接收 Illuminate\Database\Query\JoinClause 實體，我們可以使用該實體來指定「Join」子句的條件限制：

1DB::table('users')
2        ->join('contacts', function ($join) {
3            $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
4        })
5        ->get();
1DB::table('users')
2        ->join('contacts', function ($join) {
3            $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
4        })
5        ->get();

若想在 Join 上使用「Where」子句，可使用 JoinClause 實體提供的 where 與 orWhere 方法。除了直接比較兩個欄位外，也可以使用這些方法將欄位與值相比較：

1DB::table('users')
2        ->join('contacts', function ($join) {
3            $join->on('users.id', '=', 'contacts.user_id')
4                 ->where('contacts.user_id', '>', 5);
5        })
6        ->get();
1DB::table('users')
2        ->join('contacts', function ($join) {
3            $join->on('users.id', '=', 'contacts.user_id')
4                 ->where('contacts.user_id', '>', 5);
5        })
6        ->get();

子查詢的 Join

我們可以使用 joinSub、leftJoinSub、與 rightJoinSub 方法來在查詢中 Join 一個子查詢。這幾個方法都接收三個引數：子查詢、資料表別名、定義關聯欄位的閉包。在這個範例中，我們會取得一組使用者 Collection，其中各個使用者記錄中還包含該使用者最近發佈的部落格貼文之 updated_at 時戳：

1$latestPosts = DB::table('posts')
2                   ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
3                   ->where('is_published', true)
4                   ->groupBy('user_id');
5 
6$users = DB::table('users')
7        ->joinSub($latestPosts, 'latest_posts', function ($join) {
8            $join->on('users.id', '=', 'latest_posts.user_id');
9        })->get();
1$latestPosts = DB::table('posts')
2                   ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
3                   ->where('is_published', true)
4                   ->groupBy('user_id');
5 
6$users = DB::table('users')
7        ->joinSub($latestPosts, 'latest_posts', function ($join) {
8            $join->on('users.id', '=', 'latest_posts.user_id');
9        })->get();

Union

Laravel 的 Query Builder 還提供了一個可用來「Union」兩個或多個查詢的方便方法。舉例來說，我們可以先建立一個查詢，然後再使用 Union 方法來將該查詢與更多的查詢聯集(Union)起來：

1use Illuminate\Support\Facades\DB;
2 
3$first = DB::table('users')
4            ->whereNull('first_name');
5 
6$users = DB::table('users')
7            ->whereNull('last_name')
8            ->union($first)
9            ->get();
1use Illuminate\Support\Facades\DB;
2 
3$first = DB::table('users')
4            ->whereNull('first_name');
5 
6$users = DB::table('users')
7            ->whereNull('last_name')
8            ->union($first)
9            ->get();

除了 union 方法外，Laravel 的 Query Builder 還提供了一個 unionAll 方法。在使用 unionAll 方法結合的查詢中，若有重複記錄，將保留這些重複的記錄。unionAll 方法的簽章(Signature)與 union 方法相同。

基本的 Where 子句

Where 子句

我們可以使用 Query Builder 的 where 方法來將「where」子句加到查詢中。一個 where 方法的基本呼叫需要三個引數。第一個引數為欄位名稱，第二個引數為運算子，該運算子可為任何資料庫支援的運算子，第三個印數則為要與欄位值相比較的值。

舉例來說，下列查詢會取得所有 votes 欄位等於 100、且 age 欄位大於 35 的使用者：

1$users = DB::table('users')
2                ->where('votes', '=', 100)
3                ->where('age', '>', 35)
4                ->get();
1$users = DB::table('users')
2                ->where('votes', '=', 100)
3                ->where('age', '>', 35)
4                ->get();

為了方便起見，如果要驗證欄位是否 = 給定的值，我們可以直接將該值傳給 where 方法的第二個引數。這時，Laravel 會假設要使用的是 = 運算子：

1$users = DB::table('users')->where('votes', 100)->get();
1$users = DB::table('users')->where('votes', 100)->get();

與剛才提到的一樣，只要是做使用的資料庫系統所支援的運算子，我們都可以使用：

 1$users = DB::table('users')
 2                ->where('votes', '>=', 100)
 3                ->get();
 4 
 5$users = DB::table('users')
 6                ->where('votes', '<>', 100)
 7                ->get();
 8 
 9$users = DB::table('users')
10                ->where('name', 'like', 'T%')
11                ->get();
 1$users = DB::table('users')
 2                ->where('votes', '>=', 100)
 3                ->get();
 4 
 5$users = DB::table('users')
 6                ->where('votes', '<>', 100)
 7                ->get();
 8 
 9$users = DB::table('users')
10                ->where('name', 'like', 'T%')
11                ->get();

也可以傳入一組條件陣列給 where 函式。陣列中的各個元素都應為一個包含三個引數的陣列，這三個引數就是平常傳給 where 方法的值：

1$users = DB::table('users')->where([
2    ['status', '=', '1'],
3    ['subscribed', '<>', '1'],
4])->get();
1$users = DB::table('users')->where([
2    ['status', '=', '1'],
3    ['subscribed', '<>', '1'],
4])->get();

Or Where 子句

在串聯呼叫 Query Builder 的 where 方法時，「where」子句通常會使用 and 運算子串在一起。不過，我們也可以使用 orWhere 方法，以使用 or 運算子來串聯子句。orWhere 方法接受與 where 方法相同的引數：

1$users = DB::table('users')
2                    ->where('votes', '>', 100)
3                    ->orWhere('name', 'John')
4                    ->get();
1$users = DB::table('users')
2                    ->where('votes', '>', 100)
3                    ->orWhere('name', 'John')
4                    ->get();

若將「or」條件放入括號中分組，則傳入一個閉包作為 orWhere 的第一個引數：

1$users = DB::table('users')
2            ->where('votes', '>', 100)
3            ->orWhere(function($query) {
4                $query->where('name', 'Abigail')
5                      ->where('votes', '>', 50);
6            })
7            ->get();
1$users = DB::table('users')
2            ->where('votes', '>', 100)
3            ->orWhere(function($query) {
4                $query->where('name', 'Abigail')
5                      ->where('votes', '>', 50);
6            })
7            ->get();

上述範例會產生下列 SQL：

1select * from users where votes > 100 or (name = 'Abigail' and votes > 50)
1select * from users where votes > 100 or (name = 'Abigail' and votes > 50)

JSON 的 Where 子句

對於有支援 JSON 欄位型別的資料庫，Laravel 也支援查詢 JSON 欄位。目前，支援 JSON 欄位型別的資料庫包含 MySQL 5.7+、PostgreSQL、SQL Server 2016、SQLite 3.9.0 (搭配 JSON1 擴充程式) 等。若要查詢 JSON 欄位，請使用 -> 運算子：

1$users = DB::table('users')
2                ->where('preferences->dining->meal', 'salad')
3                ->get();
1$users = DB::table('users')
2                ->where('preferences->dining->meal', 'salad')
3                ->get();

也可以使用 whereJsonContains 來查詢 JSON 陣列。SQLite 資料庫目前不支援該功能：

1$users = DB::table('users')
2                ->whereJsonContains('options->languages', 'en')
3                ->get();
1$users = DB::table('users')
2                ->whereJsonContains('options->languages', 'en')
3                ->get();

若專案使用 MySQL 或 PostgreSQL 資料庫，則可遺傳一組陣列值給 whereJsonContains 方法：

1$users = DB::table('users')
2                ->whereJsonContains('options->languages', ['en', 'de'])
3                ->get();
1$users = DB::table('users')
2                ->whereJsonContains('options->languages', ['en', 'de'])
3                ->get();

也可使用 whereJsonLength 方法來以長度查詢 JSON 陣列：

1$users = DB::table('users')
2                ->whereJsonLength('options->languages', 0)
3                ->get();
4 
5$users = DB::table('users')
6                ->whereJsonLength('options->languages', '>', 1)
7                ->get();
1$users = DB::table('users')
2                ->whereJsonLength('options->languages', 0)
3                ->get();
4 
5$users = DB::table('users')
6                ->whereJsonLength('options->languages', '>', 1)
7                ->get();

額外的 Where 子句

whereBetween / orWhereBetween

whereBetween 方法檢查某個欄位的值是否介於兩個值之間：

1$users = DB::table('users')
2           ->whereBetween('votes', [1, 100])
3           ->get();
1$users = DB::table('users')
2           ->whereBetween('votes', [1, 100])
3           ->get();

whereNotBetween / orWhereNotBetween

whereNotBetween 方法檢查某個欄位的值是否不介於兩個值之間：

1$users = DB::table('users')
2                    ->whereNotBetween('votes', [1, 100])
3                    ->get();
1$users = DB::table('users')
2                    ->whereNotBetween('votes', [1, 100])
3                    ->get();

whereIn / whereNotIn / orWhereIn / orWhereNotIn

whereIn 方法可檢查給定欄位的值是否包含在給定陣列中：

1$users = DB::table('users')
2                    ->whereIn('id', [1, 2, 3])
3                    ->get();
1$users = DB::table('users')
2                    ->whereIn('id', [1, 2, 3])
3                    ->get();

whereNotIn 方法可檢查給定欄位的值是否不包含在給定陣列中：

1$users = DB::table('users')
2                    ->whereNotIn('id', [1, 2, 3])
3                    ->get();
1$users = DB::table('users')
2                    ->whereNotIn('id', [1, 2, 3])
3                    ->get();

whereNull / whereNotNull / orWhereNull / orWhereNotNull

whereNull 方法檢查給定欄位的值是否為 NULL：

1$users = DB::table('users')
2                ->whereNull('updated_at')
3                ->get();
1$users = DB::table('users')
2                ->whereNull('updated_at')
3                ->get();

whereNotNull 方法檢查給定欄位的值是否不為 NULL：

1$users = DB::table('users')
2                ->whereNotNull('updated_at')
3                ->get();
1$users = DB::table('users')
2                ->whereNotNull('updated_at')
3                ->get();

whereDate / whereMonth / whereDay / whereYear / whereTime

whereDate 方法可用來將欄位值與特定日期比較：

1$users = DB::table('users')
2                ->whereDate('created_at', '2016-12-31')
3                ->get();
1$users = DB::table('users')
2                ->whereDate('created_at', '2016-12-31')
3                ->get();

whereMonth 方法可用來將欄位值與特定月份比較：

1$users = DB::table('users')
2                ->whereMonth('created_at', '12')
3                ->get();
1$users = DB::table('users')
2                ->whereMonth('created_at', '12')
3                ->get();

whereDay 方法可用來將欄位值與特定日比較：

1$users = DB::table('users')
2                ->whereDay('created_at', '31')
3                ->get();
1$users = DB::table('users')
2                ->whereDay('created_at', '31')
3                ->get();

whereYear 方法可用來將欄位值與特定年份比較：

1$users = DB::table('users')
2                ->whereYear('created_at', '2016')
3                ->get();
1$users = DB::table('users')
2                ->whereYear('created_at', '2016')
3                ->get();

whereTime 方法可用來將欄位值與特定時間比較：

1$users = DB::table('users')
2                ->whereTime('created_at', '=', '11:20:45')
3                ->get();
1$users = DB::table('users')
2                ->whereTime('created_at', '=', '11:20:45')
3                ->get();

whereColumn / orWhereColumn

whereColumn 方法可用來檢查兩個欄位是否相等：

1$users = DB::table('users')
2                ->whereColumn('first_name', 'last_name')
3                ->get();
1$users = DB::table('users')
2                ->whereColumn('first_name', 'last_name')
3                ->get();

也可以穿入比較運算子給 whereColumn 方法：

1$users = DB::table('users')
2                ->whereColumn('updated_at', '>', 'created_at')
3                ->get();
1$users = DB::table('users')
2                ->whereColumn('updated_at', '>', 'created_at')
3                ->get();

也可以穿入一組欄位比較陣列給 whereColumn 方法。傳入的條件會使用 and 運算子組合起來：

1$users = DB::table('users')
2                ->whereColumn([
3                    ['first_name', '=', 'last_name'],
4                    ['updated_at', '>', 'created_at'],
5                ])->get();
1$users = DB::table('users')
2                ->whereColumn([
3                    ['first_name', '=', 'last_name'],
4                    ['updated_at', '>', 'created_at'],
5                ])->get();

邏輯分組

有時候，我們會需要將多個「where」子句以括號分組起來，好讓我們能以特定的邏輯分組來查詢。其實，一般來說，在使用 orWhere 時，大部分情況都應該放在括號中，以避免產生未預期的行為。若要進行邏輯分組，可傳入一個閉包給 where 方法：

1$users = DB::table('users')
2           ->where('name', '=', 'John')
3           ->where(function ($query) {
4               $query->where('votes', '>', 100)
5                     ->orWhere('title', '=', 'Admin');
6           })
7           ->get();
1$users = DB::table('users')
2           ->where('name', '=', 'John')
3           ->where(function ($query) {
4               $query->where('votes', '>', 100)
5                     ->orWhere('title', '=', 'Admin');
6           })
7           ->get();

就像這樣，傳入閉包給 where 就代表要讓 Query Builder 開啟一個條件限制的分組。該閉包會收到一個 Query Builder 實體，我們可以使用這個實體來在括號分組內設定其中要包含的條件限制。上述範例會產生這樣的 SQL：

1select * from users where name = 'John' and (votes > 100 or title = 'Admin')
1select * from users where name = 'John' and (votes > 100 or title = 'Admin')

進階的 Where 子句

Where Exists 子句

使用 whereExists 方法，我們就能撰寫「where exists」SQL 子句。whereExists 方法接受一個閉包，該閉包會收到一個 Query Builder 實體，我們可以使用該實體來定義要放在「exists」子句內的查詢：

1$users = DB::table('users')
2           ->whereExists(function ($query) {
3               $query->select(DB::raw(1))
4                     ->from('orders')
5                     ->whereColumn('orders.user_id', 'users.id');
6           })
7           ->get();
1$users = DB::table('users')
2           ->whereExists(function ($query) {
3               $query->select(DB::raw(1))
4                     ->from('orders')
5                     ->whereColumn('orders.user_id', 'users.id');
6           })
7           ->get();

上述查詢會產生下列 SQL：

1select * from users
2where exists (
3    select 1
4    from orders
5    where orders.user_id = users.id
6)
1select * from users
2where exists (
3    select 1
4    from orders
5    where orders.user_id = users.id
6)

子查詢的 Where 子句

有時候，我們會需要製作一種「Where」子句，這種「Where」子句需要將某個子查詢的結果與給定值相比較。這種情況，我們只要穿入一個閉包以及一個值給 where 方法即可。舉例來說，下列查詢會取得所有最近的「membership」為給定類型的使用者：

1use App\Models\User;
2 
3$users = User::where(function ($query) {
4    $query->select('type')
5        ->from('membership')
6        ->whereColumn('membership.user_id', 'users.id')
7        ->orderByDesc('membership.start_date')
8        ->limit(1);
9}, 'Pro')->get();
1use App\Models\User;
2 
3$users = User::where(function ($query) {
4    $query->select('type')
5        ->from('membership')
6        ->whereColumn('membership.user_id', 'users.id')
7        ->orderByDesc('membership.start_date')
8        ->limit(1);
9}, 'Pro')->get();

或者，有時候我們還需要建立將某個欄位與子查詢結果比較的「where」子句。若要將欄位與子查詢的結果比較，請傳入一個欄位名稱、運算子、以及一個閉包給 where 方法。舉例來說，下列查詢會取得所有的收入 (Income) 記錄，其中，這些收入記錄的金額 (Amount) 必須小於平均值：

1use App\Models\Income;
2 
3$incomes = Income::where('amount', '<', function ($query) {
4    $query->selectRaw('avg(i.amount)')->from('incomes as i');
5})->get();
1use App\Models\Income;
2 
3$incomes = Income::where('amount', '<', function ($query) {
4    $query->selectRaw('avg(i.amount)')->from('incomes as i');
5})->get();

Order、Group、Limit、Offset

排序

orderBy 方法

使用 orderBy 方法，我們就能將查詢的結果以給定欄位來排序。orderBy 方法的第一個引數為要排序的欄位，而第二個引數則用來判斷排序的方向，可為 asc (升冪) 或 desc (降冪)：

1$users = DB::table('users')
2                ->orderBy('name', 'desc')
3                ->get();
1$users = DB::table('users')
2                ->orderBy('name', 'desc')
3                ->get();

若要使用多個欄位來排序，只需要叫用所需次數的 orderBy 方法即可：

1$users = DB::table('users')
2                ->orderBy('name', 'desc')
3                ->orderBy('email', 'asc')
4                ->get();
1$users = DB::table('users')
2                ->orderBy('name', 'desc')
3                ->orderBy('email', 'asc')
4                ->get();

latest 與 oldest 方法

使用 latest 與 oldest 方法，我們就能輕鬆地以日期來進行排序。預設情況，會使用資料表中的 created_at 欄位來排序查詢結果。或者，也可以傳入要用來排序的欄位名稱：

1$user = DB::table('users')
2                ->latest()
3                ->first();
1$user = DB::table('users')
2                ->latest()
3                ->first();

隨機排序

使用 inRandomOrder 方法，我們就可以使用隨機順序來排序查詢的結果。舉例來說，我們可以使用這個方法來取得某個隨機的使用者：

1$randomUser = DB::table('users')
2                ->inRandomOrder()
3                ->first();
1$randomUser = DB::table('users')
2                ->inRandomOrder()
3                ->first();

移除現有的排序

reorder 方法會移除所有之前已套用到查詢上的「order by」子句：

1$query = DB::table('users')->orderBy('name');
2 
3$unorderedUsers = $query->reorder()->get();
1$query = DB::table('users')->orderBy('name');
2 
3$unorderedUsers = $query->reorder()->get();

在呼叫 reorder 方法時也可以傳入欄位名稱與方向。若有傳入欄位名稱與方向，即可移除所有已套用的「order by」子句，並在查詢上套用全新的排序設定：

1$query = DB::table('users')->orderBy('name');
2 
3$usersOrderedByEmail = $query->reorder('email', 'desc')->get();
1$query = DB::table('users')->orderBy('name');
2 
3$usersOrderedByEmail = $query->reorder('email', 'desc')->get();

分組

groupBy 與 having 方法

與方法名稱看起來一樣，groupBy 與 having 方法可用來為查詢結果分組。having 方法的簽章與 where 方法的類似：

1$users = DB::table('users')
2                ->groupBy('account_id')
3                ->having('account_id', '>', 100)
4                ->get();
1$users = DB::table('users')
2                ->groupBy('account_id')
3                ->having('account_id', '>', 100)
4                ->get();

我們也可以使用 havingBetween 方法來使用給定的範圍篩選查詢結果：

1$report = DB::table('orders')
2                ->selectRaw('count(id) as number_of_orders, customer_id')
3                ->groupBy('customer_id')
4                ->havingBetween('number_of_orders', [5, 15])
5                ->get();
1$report = DB::table('orders')
2                ->selectRaw('count(id) as number_of_orders, customer_id')
3                ->groupBy('customer_id')
4                ->havingBetween('number_of_orders', [5, 15])
5                ->get();

也可以傳入多個引數給 groupBy 方法來分組多個欄位：

1$users = DB::table('users')
2                ->groupBy('first_name', 'status')
3                ->having('account_id', '>', 100)
4                ->get();
1$users = DB::table('users')
2                ->groupBy('first_name', 'status')
3                ->having('account_id', '>', 100)
4                ->get();

若要建立更複雜的 having 陳述式，請參考 havingRaw 方法。

Limit 與 Offset

skip 與 take 方法

我們可以使用 skip 與 take 方法來限制查詢所回傳的結果數 (take)，或是在查詢中跳過特定數量的結果 (skip)：

1$users = DB::table('users')->skip(10)->take(5)->get();
1$users = DB::table('users')->skip(10)->take(5)->get();

或者，我們也可以使用 limit 與 offset 方法。這兩個方法的功能與 take 跟 skip 方法相同：

1$users = DB::table('users')
2                ->offset(10)
3                ->limit(5)
4                ->get();
1$users = DB::table('users')
2                ->offset(10)
3                ->limit(5)
4                ->get();

條件式子句

有時候，我們會想依據一些條件來決定是否套用某個查詢子句。舉例來說，我們可能會想只在連入 HTTP Request 中包含給定的輸入值時才套用 where 子句。這種情況下，只要使用 when 即可：

1$role = $request->input('role');
2 
3$users = DB::table('users')
4                ->when($role, function ($query, $role) {
5                    return $query->where('role_id', $role);
6                })
7                ->get();
1$role = $request->input('role');
2 
3$users = DB::table('users')
4                ->when($role, function ($query, $role) {
5                    return $query->where('role_id', $role);
6                })
7                ->get();

when 方法只會在第一個引數為 true 時才執行給定的閉包。若第一個引數為 false，則將不會執行該閉包。因此，在上述的範例中，只有在 role 欄位有出現在連入 Request 中，且取值為 true 值，才會叫用傳給 when 方法的閉包。

我們也可以傳入另一個閉包給 when 方法，作為其第三個引數。只有在第一個引數取值為 false 時才會被執行。為了說明使用這個功能的情況，在這裡我們用這個功能來為查詢設定預設的排序：

1$sortByVotes = $request->input('sort_by_votes');
2 
3$users = DB::table('users')
4                ->when($sortByVotes, function ($query, $sortByVotes) {
5                    return $query->orderBy('votes');
6                }, function ($query) {
7                    return $query->orderBy('name');
8                })
9                ->get();
1$sortByVotes = $request->input('sort_by_votes');
2 
3$users = DB::table('users')
4                ->when($sortByVotes, function ($query, $sortByVotes) {
5                    return $query->orderBy('votes');
6                }, function ($query) {
7                    return $query->orderBy('name');
8                })
9                ->get();

Insert 陳述式

Laravel 的 Query Builder 還提供了一個 insert 方法，可用來將資料插入到資料表中。insert 方法接受一組欄位名稱與值的陣列：

1DB::table('users')->insert([
2    'email' => '[email protected]',
3    'votes' => 0
4]);
1DB::table('users')->insert([
2    'email' => '[email protected]',
3    'votes' => 0
4]);

我們也可以傳入一組陣列的陣列來一次插入多筆記錄。其中，每個陣列都代表了要插入到資料表的一筆資料：

1DB::table('users')->insert([
2    ['email' => '[email protected]', 'votes' => 0],
3    ['email' => '[email protected]', 'votes' => 0],
4]);
1DB::table('users')->insert([
2    ['email' => '[email protected]', 'votes' => 0],
3    ['email' => '[email protected]', 'votes' => 0],
4]);

insertOrIgnore 方法在將資料插入資料庫時會忽略期間發生的錯誤：

1DB::table('users')->insertOrIgnore([
2    ['id' => 1, 'email' => '[email protected]'],
3    ['id' => 2, 'email' => '[email protected]'],
4]);
1DB::table('users')->insertOrIgnore([
2    ['id' => 1, 'email' => '[email protected]'],
3    ['id' => 2, 'email' => '[email protected]'],
4]);

Auto-Increment(自動遞增) 的 ID

若資料表有 Auto-Increment 的 ID，則可使用 insertGetId 方法來插入一筆資料，並取得該 ID：

1$id = DB::table('users')->insertGetId(
2    ['email' => '[email protected]', 'votes' => 0]
3);
1$id = DB::table('users')->insertGetId(
2    ['email' => '[email protected]', 'votes' => 0]
3);

Upsert

當指定的記錄不存在時，upsert 方法會插入該筆記錄；若記錄已存在時，則會以指定的值來更新現有記錄。該方法的第一個引數為要插入或更新的值，而第二個引數則是一組用來判斷給定記錄在資料表中是否為不重複(Unique)記錄的欄位名稱。該方法的第三個與最後一個引數為一組欄位名稱的陣列，當在資料庫中找到符合的記錄時，會更新資料庫記錄：

1DB::table('flights')->upsert([
2    ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
3    ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
4], ['departure', 'destination'], ['price']);
1DB::table('flights')->upsert([
2    ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
3    ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
4], ['departure', 'destination'], ['price']);

在上述範例中，Laravel 會嘗試插入量比記錄。若資料庫中已有相同的 depature 與 destination 欄位值，則 Laravel 會更新該筆資料的 price 欄位。

Update 陳述式

除了將資料插入資料庫外，在 Laravel 的 Query Builder 中，也可以使用 update 方法來更新現有的資料。update 方法與 insert 方法類似，接受一組欄位／值配對的陣列，用來代表要更新的欄位。update 方法會回傳受影響的行數。我們可以使用 where 子句來對 update 查詢做條件限制：

1$affected = DB::table('users')
2              ->where('id', 1)
3              ->update(['votes' => 1]);
1$affected = DB::table('users')
2              ->where('id', 1)
3              ->update(['votes' => 1]);

Update Or Insert

有時候，我們會想更新資料庫內現有的資料，但如果資料庫中還沒有這筆資料的話，就建立一筆新的資料。這時，可以使用 updateOrInsert 方法。updateOrInsert 方法接受兩個因數：一組用來尋找資料的陣列，以及一組用來表示欄位更新的欄位／值配對陣列。

updateOrInsert 方法會試著使用第一個引數的欄位／值配對來找到符合的資料。若有找到資料，則 Query Builder 會使用第二個引數內的值來更新該資料；若找不到資料，則會將這兩個引數合併，並插入到資料庫中：

1DB::table('users')
2    ->updateOrInsert(
3        ['email' => '[email protected]', 'name' => 'John'],
4        ['votes' => '2']
5    );
1DB::table('users')
2    ->updateOrInsert(
3        ['email' => '[email protected]', 'name' => 'John'],
4        ['votes' => '2']
5    );

更新 JSON 欄位

在更新 JSON 欄位時，應使用 -> 格式來更新 JSON 物件中對應的索引鍵。更新 JSON 物件索引鍵支援 MySQL 5.7 版以上與 PostgreSQL 9.5 版以上：

1$affected = DB::table('users')
2              ->where('id', 1)
3              ->update(['options->enabled' => true]);
1$affected = DB::table('users')
2              ->where('id', 1)
3              ->update(['options->enabled' => true]);

遞增與遞減

Laravel 的 Query Builder 還提供了用來遞增與遞減給定欄位值的方便方法。這幾個方法都接受至少一個引數：要修改的欄位名稱。也可以提供第二個引數，來指定該欄位要遞增或遞減多少：

1DB::table('users')->increment('votes');
2 
3DB::table('users')->increment('votes', 5);
4 
5DB::table('users')->decrement('votes');
6 
7DB::table('users')->decrement('votes', 5);
1DB::table('users')->increment('votes');
2 
3DB::table('users')->increment('votes', 5);
4 
5DB::table('users')->decrement('votes');
6 
7DB::table('users')->decrement('votes', 5);

在遞增或遞減時，也可以指定其他要更新的欄位：

1DB::table('users')->increment('votes', 1, ['name' => 'John']);
1DB::table('users')->increment('votes', 1, ['name' => 'John']);

Delete 陳述式

在 Laravel 的 Query Builder 中，可使用 delete 方法來將資料從資料表中刪除。delete 方法會回傳受影響的行數。我們可以在呼叫 delete 方法前新增「where」子句來對 delete 陳述式做條件限制：

1$deleted = DB::table('users')->delete();
2 
3$deleted = DB::table('users')->where('votes', '>', 100)->delete();
1$deleted = DB::table('users')->delete();
2 
3$deleted = DB::table('users')->where('votes', '>', 100)->delete();

若想 Truncate(截斷) 整張資料表，也就是從資料表中移除所有資料，並將 Auto-Increment 的 ID 重設為 0，則可使用 truncate 方法：

1DB::table('users')->truncate();
1DB::table('users')->truncate();

Truncate 資料表與 PostgreSQL

在 PostgreSQL 資料庫中 Truncate 資料表時，會套用 CASCADE 行為。這表示，這張資料表中若有與其他資料表使用外部索引鍵關聯，則其他資料表上的資料也會被刪除。

悲觀鎖定

Laravel 的 Query Builder 中，還包含了一些能讓我們在執行 select 陳述式時進行「悲觀鎖定(Pessimistic Locking)」的功能。若要以「Shared Lock」執行陳述式，可以呼叫 sharedLock 方法。使用 Shared Lock 可防止 Select 陳述式所取得的資料在 Transaction 被 Commit 前都不被修改：

1DB::table('users')
2        ->where('votes', '>', 100)
3        ->sharedLock()
4        ->get();
1DB::table('users')
2        ->where('votes', '>', 100)
3        ->sharedLock()
4        ->get();

或者，我們也可以使用 lockForUpdate 方法。「For Update」Lock 可防止 Select 陳述式所取得的資料被修改，並且讓其他 Shared Lock 無法 Select 該資料：

1DB::table('users')
2        ->where('votes', '>', 100)
3        ->lockForUpdate()
4        ->get();
1DB::table('users')
2        ->where('votes', '>', 100)
3        ->lockForUpdate()
4        ->get();

偵錯

在建立查詢時，可以使用 dd 與 dump 方法來將目前的查詢繫結於 SQL 傾印出來。dd 方法會顯示偵錯資訊，然後停止執行該 Request。dump 方法會顯示出偵錯資訊，並讓 Request 繼續執行：

1DB::table('users')->where('votes', '>', 100)->dd();
2 
3DB::table('users')->where('votes', '>', 100)->dump();
1DB::table('users')->where('votes', '>', 100)->dd();
2 
3DB::table('users')->where('votes', '>', 100)->dump();