查詢建構器


資料庫：查詢建構器

    ##簡介
    • #取得結果
    • 分塊結果
    • ##查詢
    • 原生運算式
    • #Joins
    • Unions
    • Where 語句
    • 參數分組
      • Where Exists 語法
      • JSON Where 語法
    • Ordering, Grouping, Limit, & Offset
    • 條件語句
    • #插入
    • 更新
    • #更新JSON
      • 自增和自減
    • 刪除
    • #悲觀鎖定

簡介

Laravel 的資料庫查詢建構器為建立和執行資料庫查詢提供了一個方便的介面。它可用於執行應用程式中大部分資料庫操作，且可在所有支援的資料庫系統上執行。

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 來終止繼續取得分塊結果：

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 建立一個原生表達式：

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

{提示} 原生表達式將會被當做字串注入到查詢中，因此你應該小心使用，避免建立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方法。若要執行基本的
“內連結”，你可以在查詢建構器實例上使用 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

查詢建構器也提供了將兩個查詢 “聯合” 的捷徑。例如，你可以先建立一個查詢，然後使用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();

#參數分組
有時候你需要建立更進階的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();

條件語句

有時候你可能想要子句只適用於某個情況為真是才執行查詢。例如你可能只想給定值在請求中存在的情況下才套用  where 語句。你可以用 when 方法：

$role = $request->input('role');$users = DB::table('users')             
   ->when($role, function ($query, $role) {              
         return $query->where('role_id', $role);             
        })                
    ->get();

when 方法只有在第一個參數為true 的時候才執行給的的閉包。如果第一個參數為false ，那麼這個閉包將不會被執行

你可以傳遞另一個閉包作為when 方法的第三個參數。該閉包會在第一個參數為 false 的情況下執行。為了說明如何使用這個特性，我們來配置一個查詢的預設排序：

$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]);

更新或新增

有時您可能希望更新資料庫中的現有記錄，或者如果不存在匹配記錄則建立它。在這種情況下，可以使用 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();
本文章首發在 LearnKu.com 網站上。
←
→