查詢構造器
查詢構造器
數據庫：查詢構造器
簡介
Laravel 的數據庫查詢構造器為創(chuàng)建和運行數據庫查詢提供了一個方便的接口。它可用于執(zhí)行應用程序中大部分數據庫操作，且可在所有支持的數據庫系統上運行。
Laravel 的查詢構造器使用 PDO 參數綁定來保護您的應用程序免受 SQL 注入攻擊。因此沒有必要清理作為綁定傳遞的字符串。
獲取結果
從一個數據表中獲取所有行
你可以 DB facade 上使用 table 方法來開始查詢。該 table 方法為給定的表返回一個查詢構造器實例，允許你在查詢上鏈式調用更多的約束，最后使用 get 方法獲取結果：
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;
class UserController extends Controller{
/**
* 顯示所有應用程序的用戶列表。
*
* @return Response
*/
public function index()
{
$users = DB::table('users')->get();
return view('user.index', ['users' => $users]);
}
}該 get 方法返回一個包含 Illuminate\Support\Collection 的結果，其中每個結果都是 PHP StdClass 對象的一個實例。你可以訪問字段作為對象的屬性來訪問每列的值：
foreach ($users as $user) {
echo $user->name;
}從數據表中獲取單行或單列
如果你只需要從數據表中獲取一行數據，你可以使用 first 方法。該方法返回一個 StdClass 對象：
$user = DB::table('users')->where('name', 'John')->first();
echo $user->name;如果你甚至不需要整行數據，則可以使用 value 方法從記錄中獲取單個值。該方法將直接返回該字段的值：
$email = DB::table('users')->where('name', 'John')->value('email');獲取一列的值
如果你想獲取包含單列值的集合，則可以使用 pluck 方法。在下面的例子中，我們將獲取角色表中標題的集合：
$titles = DB::table('roles')->pluck('title');
foreach ($titles as $title) {
echo $title;
}你還可以在返回的集合中指定字段的自定義鍵值：
$roles = DB::table('roles')->pluck('title', 'name');
foreach ($roles as $name => $title) {
echo $title;
}分塊結果
如果你需要處理上千條數據庫記錄，你可以考慮使用 chunk 方法。該方法一次獲取結果集的一小塊，并將其傳遞給 閉包 函數進行處理。該方法在 Artisan 命令 編寫數千條處理數據的時候非常有用。例如，我們可以將全部 users 表數據切割成一次處理 100 條記錄的一小塊：
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
foreach ($users as $user) {
//
}
});你可以通過在 閉包 中返回 false 來終止繼續(xù)獲取分塊結果：
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
// Process the records...
return false;
});如果要在分塊結果時更新數據庫記錄，則塊結果可能會和預計的返回結果不一致。 因此，在分塊更新記錄時，最好使用 chunkById 方法。 此方法將根據記錄的主鍵自動對結果進行分頁：
DB::table('users')->where('active', false)
->chunkById(100, function ($users) {
foreach ($users as $user) {
DB::table('users')
->where('id', $user->id)
->update(['active' => true]);
}
});{提示} 在塊的回調里面更新或刪除記錄時，對主鍵或外鍵的任何更改都可能影響塊查詢。 這可能會導致記錄沒有包含在分塊結果中。
聚合
查詢構造器還提供了各種聚合方法，比如 count, max，min， avg，還有 sum。你可以在構造查詢后調用任何方法：
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');當然，你也可以將這些聚合方法與其他的查詢語句相結合：
$price = DB::table('orders')
->where('finalized', 1)
->avg('price');判斷記錄是否存在
除了通過 count 方法可以確定查詢條件的結果是否存在之外，還可以使用 exists 和 doesntExist 方法：
return DB::table('orders')->where('finalized', 1)->exists();
return DB::table('orders')->where('finalized', 1)->doesntExist();Selects
指定一個 Select 語句
當然你可能并不總是希望從數據庫表中獲取所有列。使用 select 方法，你可以自定義一個 select 查詢語句來查詢指定的字段：
$users = DB::table('users')->select('name', 'email as user_email')->get();distinct 方法會強制讓查詢返回的結果不重復：
$users = DB::table('users')->distinct()->get();如果你已經有了一個查詢構造器實例，并且希望在現有的查詢語句中加入一個字段，那么你可以使用 addSelect 方法：
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();原生表達式
有時候你可能需要在查詢中使用原生表達式。你可以使用 DB::raw 創(chuàng)建一個原生表達式：
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();{提示} 原生表達式將會被當做字符串注入到查詢中，因此你應該小心使用，避免創(chuàng)建 SQL 注入的漏洞。
原生方法
可以使用以下方法代替 DB::raw，將原生表達式插入查詢的各個部分。
selectRaw
selectRaw 方法可以代替 select(DB::raw(...))。該方法的第二個參數是可選項，值是一個綁定參數的數組：
$orders = DB::table('orders')
->selectRaw('price * ? as price_with_tax', [1.0825])
->get();whereRaw / orWhereRaw
whereRaw 和 orWhereRaw 方法將原生的 where
注入到你的查詢中。這兩個方法的第二個參數還是可選項，值還是綁定參數的數組：
$orders = DB::table('orders')
->whereRaw('price > IF(state = "TX", ?, 100)', [200])
->get();havingRaw / orHavingRaw
havingRaw 和 orHavingRaw 方法可以用于將原生字符串設置為 having 語句的值：
$orders = DB::table('orders')
->select('department', DB::raw('SUM(price) as total_sales'))
->groupBy('department')
->havingRaw('SUM(price) > ?', [2500])
->get();orderByRaw
orderByRaw 方法可用于將原生字符串設置為 order by 子句的值：
$orders = DB::table('orders')
->orderByRaw('updated_at - created_at DESC')
->get();Joins
Inner Join Clause
查詢構造器也可以編寫 join 方法。若要執(zhí)行基本的
「內鏈接」，你可以在查詢構造器實例上使用 join 方法。傳遞給 join 方法的第一個參數是你需要連接的表的名稱，而其他參數則使用指定連接的字段約束。你還可以在單個查詢中連接多個數據表：
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();Left Join 語句
如果你想使用 「左連接」或者 「右連接」代替「內連接」 ，可以使用 leftJoin 或者 rightJoin 方法。這兩個方法與 join 方法用法相同：
$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get(); $users = DB::table('users')
->rightJoin('posts', 'users.id', '=', 'posts.user_id')
->get();Cross Join 語句
使用 crossJoin 方法和你想要連接的表名做 「交叉連接」。交叉連接在第一個表和被連接的表之間會生成笛卡爾積：
$users = DB::table('sizes')
->crossJoin('colours')
->get();高級 Join 語句
你可以指定更高級的 join 語句。比如傳遞一個 閉包 作為 join 方法的第二個參數。此 閉包 接收一個JoinClause 對象，從而指定 join 語句中指定的約束：
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
})
->get();如果你想要在連接上使用「where」 風格的語句，你可以在連接上使用 where 和 orWhere 方法。這些方法會將列和值進行比較，而不是列和列進行比較：
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')
->where('contacts.user_id', '>', 5);
})
->get();子連接查詢
你可以使用 joinSub，leftJoinSub 和 rightJoinSub 方法關聯一個查詢作為子查詢。他們每一種方法都會接收三個參數：子查詢，表別名和定義關聯字段的閉包：
$latestPosts = DB::table('posts')
->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
->where('is_published', true)
->groupBy('user_id');$users = DB::table('users')
->joinSub($latestPosts, 'latest_posts', function($join) {
$join->on('users.id', '=', 'latest_posts.user_id');
})
->get();Unions
查詢構造器還提供了將兩個查詢 「聯合」 的快捷方式。比如，你可以先創(chuàng)建一個查詢，然后使用 union 方法將其和第二個查詢進行聯合：
$first = DB::table('users')
->whereNull('first_name');$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();{提示} 你也可以使用
unionAll方法，用法union方法是的一樣。
Where 語句
簡單的 Where 語句
在構造 where 查詢實例的中，你可以使用 where 方法。調用 where 最基本的方式是需要傳遞三個參數：第一個參數是列名，第二個參數是任意一個數據庫系統支持的運算符，第三個是該列要比較的值。
例如，下面是一個要驗證 「votes」 字段的值等于 100 的查詢：
$users = DB::table('users')->where('votes', '=', 100)->get();為了方便，如果你只是簡單比較列值和給定數值是否相等，可以將數值直接作為 where 方法的第二個參數：
$users = DB::table('users')->where('votes', 100)->get();當然，你也可以使用其他的運算符來編寫 where 子句：
$users = DB::table('users')
->where('votes', '>=', 100)
->get();$users = DB::table('users')
->where('votes', '<>', 100)
->get();$users = DB::table('users')
->where('name', 'like', 'T%')
->get();你還可以傳遞條件數組到 where 函數中：
$users = DB::table('users')->where([
['status', '=', '1'],
['subscribed', '<>', '1'],
])->get();Or 語句
你可以一起鏈式調用 where 約束，也可以在查詢中添加 or 字句。 orWhere 方法和 where 方法接收的參數一樣：
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();其他 Where 語句
whereBetween
whereBetween 方法驗證字段值是否在給定的兩個值之間：
$users = DB::table('users')
->whereBetween('votes', [1, 100])->get();whereNotBetween
whereNotBetween 方法驗證字段值是否在給定的兩個值之外：
$users = DB::table('users')
->whereNotBetween('votes', [1, 100])
->get();whereIn / whereNotIn
whereIn 方法驗證字段的值必須存在指定的數組里，:
$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();whereNotIn 方法驗證字段的值必須不存在于指定的數組里:
$users = DB::table('users')
->whereNotIn('id', [1, 2, 3])
->get();whereNull / whereNotNull
whereNull 方法驗證指定的字段必須是 NULL:
$users = DB::table('users')
->whereNull('updated_at')
->get();whereNotNull 方法驗證指定的字段必須不是 NULL:
$users = DB::table('users')
->whereNotNull('updated_at')
->get();whereDate / whereMonth / whereDay / whereYear / whereTime
whereDate 方法用于比較字段值與給定的日期:
$users = DB::table('users')
->whereDate('created_at', '2018-09-08')
->get();whereMonth 方法用于比較字段值與一年中指定的月份:
$users = DB::table('users')
->whereMonth('created_at', '9')
->get();whereDay 方法用于比較字段值與一月中指定的日期:
$users = DB::table('users')
->whereDay('created_at', '8')
->get();whereYear 方法用于比較字段值與指定的年份:
$users = DB::table('users')
->whereYear('created_at', '2018')
->get();whereTime 方法用于比較字段值與指定的時間（時分秒）:
$users = DB::table('users')
->whereTime('created_at', '=', '11:20:45')
->get();whereColumn
whereColumn 方法用于比較兩個字段的值 是否相等:
$users = DB::table('users')
->whereColumn('first_name', 'last_name')
->get();你也可以傳入一個比較運算符:
$users = DB::table('users')
->whereColumn('updated_at', '>', 'created_at')
->get();whereColumn 你也可以傳遞數組 用 and 運算符鏈接:
$users = DB::table('users')
->whereColumn([
['first_name', '=', 'last_name'],
['updated_at', '>', 'created_at']
])->get();參數分組
有時候你需要創(chuàng)建更高級的 where 子句，例如「where exists」或者嵌套的參數分組。 Laravel 的查詢構造器也能夠處理這些。下面，讓我們看一個在括號中進行分組約束的例子:
DB::table('users')
->where('name', '=', 'John')
->where(function ($query) {
$query->where('votes', '>', 100)
->orWhere('title', '=', 'Admin');
})
->get();你可以看到，通過一個 Closure 寫入 where 方法構建一個查詢構造器 來約束一個分組。這個 Closure 接收一個查詢實例，你可以使用這個實例來設置應該包含的約束。上面的例子將生成以下 SQL:
select * from users where name = 'John' and (votes > 100 or title = 'Admin')
{提示} 你應該用
orWhere調用這個分組，以避免應用全局作用出現意外.
Where Exists 語句
whereExists 方法允許你使用 where exists SQL 語句。 whereExists 方法接收一個 Closure 參數，該 whereExists 方法接受一個 Closure 參數，該閉包獲取一個查詢構建器實例從而允許你定義放置在 exists 字句中查詢：
DB::table('users')
->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.user_id = users.id');
})
->get();上述查詢將產生如下的 SQL 語句：
select * from users where exists ( select 1 from orders where orders.user_id = users.id)
JSON Where 語句
Laravel 也支持查詢 JSON 類型的字段（僅在對 JSON 類型支持的數據庫上）。目前，本特性僅支持 MySQL 5.7、PostgreSQL、SQL Server 2016 以及 SQLite 3.9.0 (with the JSON1 extension)。使用 -> 操作符查詢 JSON 數據：
$users = DB::table('users')
->where('options->language', 'en')
->get();$users = DB::table('users')
->where('preferences->dining->meal', 'salad')
->get();你也可以使用 whereJsonContains 來查詢 JSON 數組：
$users = DB::table('users')
->whereJsonContains('options->languages', 'en')
->get();MySQL 和 PostgreSQL 的 whereJsonContains 可以支持多個值：
$users = DB::table('users')
->whereJsonContains('options->languages', ['en', 'de'])
->get();你可以使用 whereJsonLength 來查詢 JSON 數組的長度：
$users = DB::table('users')
->whereJsonLength('options->languages', 0)
->get();$users = DB::table('users')
->whereJsonLength('options->languages', '>', 1)
->get();Ordering, Grouping, Limit, & Offset
orderBy
orderBy 方法允許你通過給定字段對結果集進行排序。 orderBy 的第一個參數應該是你希望排序的字段，第二個參數控制排序的方向，可以是 asc 或 desc：
$users = DB::table('users')
->orderBy('name', 'desc')
->get();latest / oldest
latest 和 oldest 方法可以使你輕松地通過日期排序。它默認使用 created_at 列作為排序依據。當然，你也可以傳遞自定義的列名：
$user = DB::table('users')
->latest()
->first();inRandomOrder
inRandomOrder 方法被用來將結果隨機排序。例如，你可以使用此方法隨機找到一個用戶。
$randomUser = DB::table('users')
->inRandomOrder()
->first();groupBy / having
groupBy 和 having 方法可以將結果分組。 having 方法的使用與 where 方法十分相似：
$users = DB::table('users')
->groupBy('account_id')
->having('account_id', '>', 100)
->get();你可以向 groupBy 方法傳遞多個參數：
$users = DB::table('users')
->groupBy('first_name', 'status')
->having('account_id', '>', 100)
->get();對于更高級的 having 語法，參見 havingRaw 方法。
skip / take
要限制結果的返回數量，或跳過指定數量的結果，你可以使用 skip 和 take 方法：
$users = DB::table('users')->skip(10)->take(5)->get();或者你也可以使用 limit 和 offset 方法：
$users = DB::table('users')
->offset(10)
->limit(5)
->get();條件語句
有時候你可能想要子句只適用于某個情況為真是才執(zhí)行查詢。例如你可能只想給定值在請求中存在的情況下才應用 where 語句。 你可以通過使用 when 方法：
$role = $request->input('role');$users = DB::table('users')
->when($role, function ($query, $role) {
return $query->where('role_id', $role);
})
->get();when 方法只有在第一個參數為 true 的時候才執(zhí)行給的的閉包。如果第一個參數為 false ，那么這個閉包將不會被執(zhí)行
你可以傳遞另一個閉包作為 when 方法的第三個參數。 該閉包會在第一個參數為 false 的情況下執(zhí)行。為了說明如何使用這個特性，我們來配置一個查詢的默認排序：
$sortBy = null;$users = DB::table('users')
->when($sortBy, function ($query, $sortBy) {
return $query->orderBy($sortBy);
}, function ($query) {
return $query->orderBy('name');
})
->get();插入
查詢構造器還提供了 insert 方法用于插入記錄到數據庫中。 insert 方法接收數組形式的字段名和字段值進行插入操作：
DB::table('users')->insert( ['email' => 'john@example.com', 'votes' => 0]);你甚至可以將數組傳遞給 insert 方法，將多個記錄插入到表中
DB::table('users')->insert([
['email' => 'taylor@example.com', 'votes' => 0],
['email' => 'dayle@example.com', 'votes' => 0]
]);自增 ID
如果數據表有自增 ID ，使用 insertGetId 方法來插入記錄并返回 ID 值
$id = DB::table('users')->insertGetId(
['email' => 'john@example.com', 'votes' => 0]
);{注意} 當使用 PostgreSQL 時，
insertGetId方法將默認把id作為自動遞增字段的名稱。如果你要從其他「序列」來獲取 ID ，則可以將字段名稱作為第二個參數傳遞給insertGetId方法。
更新
當然， 除了插入記錄到數據庫中，查詢構造器也可以通過 update 方法更新已有的記錄。 update 方法和 insert 方法一樣，接受包含要更新的字段及值的數組。你可以通過 where 子句對 update 查詢進行約束：
DB::table('users')
->where('id', 1)
->update(['votes' => 1]);更新或者新增
有時您可能希望更新數據庫中的現有記錄，或者如果不存在匹配記錄則創(chuàng)建它。 在這種情況下，可以使用 updateOrInsert 方法。 updateOrInsert 方法接受兩個參數：一個用于查找記錄的條件數組，以及一個包含要更該記錄的鍵值對數組。
updateOrInsert 方法將首先嘗試使用第一個參數的鍵和值對來查找匹配的數據庫記錄。 如果記錄存在，則使用第二個參數中的值去更新記錄。 如果找不到記錄，將插入一個新記錄，更新的數據是兩個數組的集合：
DB::table('users')
->updateOrInsert(
['email' => 'john@example.com', 'name' => 'John'],
['votes' => '2']
);更新 JSON 字段
更新 JSON 字段時，你可以使用 -> 語法訪問 JSON 對象中相應的值，此操作只能支持 MySQL 5.7+：
DB::table('users')
->where('id', 1)
->update(['options->enabled' => true]);自增與自減
查詢構造器還為給定字段的遞增或遞減提供了方便的方法。此方法提供了一個比手動編寫 update 語句更具表達力且更精練的接口。
這兩種方法都至少接收一個參數：需要修改的列。第二個參數是可選的，用于控制列遞增或遞減的量：
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);你也可以在操作過程中指定要更新的字段：
DB::table('users')->increment('votes', 1, ['name' => 'John']);刪除
查詢構造器也可以使用 delete 方法從表中刪除記錄。 在使用 delete 前，可以添加 where 子句來約束 delete 語法：
DB::table('users')->delete();
DB::table('users')->where('votes', '>', 100)->delete();如果你需要清空表，你可以使用 truncate 方法，它將刪除所有行，并重置自增 ID 為零：
DB::table('users')->truncate();悲觀鎖
查詢構造器也包含一些可以幫助你在 select 語法上實現「悲觀鎖定」的函數。若想在查詢中實現一個「共享鎖」， 你可以使用 sharedLock 方法。 共享鎖可防止選中的數據列被篡改，直到事務被提交為止 ：
DB::table('users')->where('votes', '>', 100)->sharedLock()->get();或者，你可以使用 lockForUpdate 方法。使用 「update」鎖可避免行被其它共享鎖修改或選取：
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();