資料庫：Query Builder

• 簡介

• 執行資料庫查詢

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

• Select 陳述式

• 原始陳述式

• Join

• Union

• 基礎的 Where 子句

  • Where 子句
  • Or Where 子句
  • Where Not 子句
  • Where Any / All Clauses
  • JSON Where 子句
  • 其他 Where 子句
  • 邏輯群組

• 進階 Where 子句

  • Where Exists 子句
  • Subquery Where 子句
  • 全文 Where 子句

• Ordering, Grouping, Limit and Offset

  • Order
  • Group
  • Limit and Offset

• 條件式子句

• Insert 陳述式

  • Upsert

• Update 陳述式

  • 更新 JSON 欄位
  • Increment and Decrement

• Delete 陳述式

• 悲觀鎖定

• 偵錯

簡介

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

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

執行資料庫查詢

Retrieving All Rows From a Table

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

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

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}

Retrieving a Single Row / Column From a Table

若只需要從資料庫資料表中取得單一行的話，可使用 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);

Retrieving a List of Column Values

若想將某個欄位的所有值放在 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\Collection;
2use Illuminate\Support\Facades\DB;
3 
4DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
5    foreach ($users as $user) {
6        // ...
7    }
8});
1use Illuminate\Support\Collection;
2use Illuminate\Support\Facades\DB;
3 
4DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
5    foreach ($users as $user) {
6        // ...
7    }
8});

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

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

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

1DB::table('users')->where('active', false)
2    ->chunkById(100, function (Collection $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 (Collection $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 (object $user) {
4    // ...
5});
1use Illuminate\Support\Facades\DB;
2 
3DB::table('users')->orderBy('id')->lazy()->each(function (object $user) {
4    // ...
5});

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

1DB::table('users')->where('active', false)
2    ->lazyById()->each(function (object $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 (object $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');

Determining if Records Exist

我們不需要使用 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 陳述式

Specifying a Select Clause

我們不是每次都想把資料表上所有的欄位都抓下來。使用 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 (JoinClause $join) {
3            $join->on('users.id', '=', 'contacts.user_id')->orOn(/* ... */);
4        })
5        ->get();
1DB::table('users')
2        ->join('contacts', function (JoinClause $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 (JoinClause $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 (JoinClause $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 (JoinClause $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 (JoinClause $join) {
8            $join->on('users.id', '=', 'latest_posts.user_id');
9        })->get();

Lateral Joins

You may use the joinLateral and leftJoinLateral methods to perform a "lateral join" with a subquery. Each of these methods receives two arguments: the subquery and its table alias. The join condition(s) should be specified within the where clause of the given subquery. Lateral joins are evaluated for each row and can reference columns outside the subquery.

In this example, we will retrieve a collection of users as well as the user's three most recent blog posts. Each user can produce up to three rows in the result set: one for each of their most recent blog posts. The join condition is specified with a whereColumn clause within the subquery, referencing the current user row:

1$latestPosts = DB::table('posts')
2                   ->select('id as post_id', 'title as post_title', 'created_at as post_created_at')
3                   ->whereColumn('user_id', 'users.id')
4                   ->orderBy('created_at', 'desc')
5                   ->limit(3);
6 
7$users = DB::table('users')
8            ->joinLateral($latestPosts, 'latest_posts')
9            ->get();
1$latestPosts = DB::table('posts')
2                   ->select('id as post_id', 'title as post_title', 'created_at as post_created_at')
3                   ->whereColumn('user_id', 'users.id')
4                   ->orderBy('created_at', 'desc')
5                   ->limit(3);
6 
7$users = DB::table('users')
8            ->joinLateral($latestPosts, 'latest_posts')
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 (Builder $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 (Builder $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)

Where Not 子句

whereNot 與 orWhereNot 方法可用來否定給定的查詢條件群組。舉例來說，下列查詢會排除所有目前為 Clearance (清倉)，且價格 (Price) 小於 10 的商品 (Product)：

1$products = DB::table('products')
2                ->whereNot(function (Builder $query) {
3                    $query->where('clearance', true)
4                          ->orWhere('price', '<', 10);
5                })
6                ->get();
1$products = DB::table('products')
2                ->whereNot(function (Builder $query) {
3                    $query->where('clearance', true)
4                          ->orWhere('price', '<', 10);
5                })
6                ->get();

Where Any / All Clauses

Sometimes you may need to apply the same query constraints to multiple columns. For example, you may want to retrieve all records where any columns in a given list are LIKE a given value. You may accomplish this using the whereAny method:

1$users = DB::table('users')
2            ->where('active', true)
3            ->whereAny([
4                'name',
5                'email',
6                'phone',
7            ], 'LIKE', 'Example%')
8            ->get();
1$users = DB::table('users')
2            ->where('active', true)
3            ->whereAny([
4                'name',
5                'email',
6                'phone',
7            ], 'LIKE', 'Example%')
8            ->get();

The query above will result in the following SQL:

1SELECT *
2FROM users
3WHERE active = true AND (
4    name LIKE 'Example%' OR
5    email LIKE 'Example%' OR
6    phone LIKE 'Example%'
7)
1SELECT *
2FROM users
3WHERE active = true AND (
4    name LIKE 'Example%' OR
5    email LIKE 'Example%' OR
6    phone LIKE 'Example%'
7)

Similarly, the whereAll method may be used to retrieve records where all of the given columns match a given constraint:

1$posts = DB::table('posts')
2            ->where('published', true)
3            ->whereAll([
4                'title',
5                'content',
6            ], 'LIKE', '%Laravel%')
7            ->get();
1$posts = DB::table('posts')
2            ->where('published', true)
3            ->whereAll([
4                'title',
5                'content',
6            ], 'LIKE', '%Laravel%')
7            ->get();

The query above will result in the following SQL:

1SELECT *
2FROM posts
3WHERE published = true AND (
4    title LIKE '%Laravel%' AND
5    content LIKE '%Laravel%'
6)
1SELECT *
2FROM posts
3WHERE published = true AND (
4    title LIKE '%Laravel%' AND
5    content LIKE '%Laravel%'
6)

JSON 的 Where 子句

對於有支援 JSON 欄位型別的資料庫，Laravel 也支援查詢 JSON 欄位。目前，支援 JSON 欄位型別的資料庫包含 MySQL 5.7+、PostgreSQL、SQL Server 2016、SQLite 3.39.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();

You may use whereJsonContains to query JSON arrays:

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();

whereBetweenColumns / whereNotBetweenColumns / orWhereBetweenColumns / orWhereNotBetweenColumns

whereBetweenColumns 方法會驗證欄位值是否介於資料表中同一行的兩個欄位值之間：

1$patients = DB::table('patients')
2                       ->whereBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
3                       ->get();
1$patients = DB::table('patients')
2                       ->whereBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
3                       ->get();

whereNotBetweenColumns 方法會驗證欄位值是否不在資料表中同一行的兩個欄位值之間：

1$patients = DB::table('patients')
2                       ->whereNotBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
3                       ->get();
1$patients = DB::table('patients')
2                       ->whereNotBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
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();

也可以提供查詢物件作為 whereIn 方法的第二個引數：

1$activeUsers = DB::table('users')->select('id')->where('is_active', 1);
2 
3$users = DB::table('comments')
4                    ->whereIn('user_id', $activeUsers)
5                    ->get();
1$activeUsers = DB::table('users')->select('id')->where('is_active', 1);
2 
3$users = DB::table('comments')
4                    ->whereIn('user_id', $activeUsers)
5                    ->get();

上述範例會產生下列 SQL：

1select * from comments where user_id in (
2    select id
3    from users
4    where is_active = 1
5)
1select * from comments where user_id in (
2    select id
3    from users
4    where is_active = 1
5)

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 (Builder $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 (Builder $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 (Builder $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 (Builder $query) {
3               $query->select(DB::raw(1))
4                     ->from('orders')
5                     ->whereColumn('orders.user_id', 'users.id');
6           })
7           ->get();

或者，除了閉包外，也可以提供一個 Query 物件給 whereExists 方法：

1$orders = DB::table('orders')
2                ->select(DB::raw(1))
3                ->whereColumn('orders.user_id', 'users.id');
4 
5$users = DB::table('users')
6                    ->whereExists($orders)
7                    ->get();
1$orders = DB::table('orders')
2                ->select(DB::raw(1))
3                ->whereColumn('orders.user_id', 'users.id');
4 
5$users = DB::table('users')
6                    ->whereExists($orders)
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;
 2use Illuminate\Database\Query\Builder;
 3 
 4$users = User::where(function (Builder $query) {
 5    $query->select('type')
 6        ->from('membership')
 7        ->whereColumn('membership.user_id', 'users.id')
 8        ->orderByDesc('membership.start_date')
 9        ->limit(1);
10}, 'Pro')->get();
 1use App\Models\User;
 2use Illuminate\Database\Query\Builder;
 3 
 4$users = User::where(function (Builder $query) {
 5    $query->select('type')
 6        ->from('membership')
 7        ->whereColumn('membership.user_id', 'users.id')
 8        ->orderByDesc('membership.start_date')
 9        ->limit(1);
10}, 'Pro')->get();

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

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

全文 Where 子句

使用 whereFullText 與 orWhereFullText 方法，就可在查詢中為有全文索引的欄位加上全文「where」子句。Laravel 會依據底層的資料庫系統將這些方法轉換為適當的 SQL。舉例來說，使用 MySQL 的專案會產生 MATCH AGAINST 子句：

1$users = DB::table('users')
2           ->whereFullText('bio', 'web developer')
3           ->get();
1$users = DB::table('users')
2           ->whereFullText('bio', 'web developer')
3           ->get();

Ordering, Grouping, Limit and 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();

The latest and oldest Methods

使用 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();

分組

The groupBy and having Methods

與方法名稱看起來一樣，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 and Offset

The skip and take Methods

我們可以使用 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->string('role');
2 
3$users = DB::table('users')
4                ->when($role, function (Builder $query, string $role) {
5                    $query->where('role_id', $role);
6                })
7                ->get();
1$role = $request->string('role');
2 
3$users = DB::table('users')
4                ->when($role, function (Builder $query, string $role) {
5                    $query->where('role_id', $role);
6                })
7                ->get();

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

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

1$sortByVotes = $request->boolean('sort_by_votes');
2 
3$users = DB::table('users')
4                ->when($sortByVotes, function (Builder $query, bool $sortByVotes) {
5                    $query->orderBy('votes');
6                }, function (Builder $query) {
7                    $query->orderBy('name');
8                })
9                ->get();
1$sortByVotes = $request->boolean('sort_by_votes');
2 
3$users = DB::table('users')
4                ->when($sortByVotes, function (Builder $query, bool $sortByVotes) {
5                    $query->orderBy('votes');
6                }, function (Builder $query) {
7                    $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]);