Laravel Query Builder 原理及用法

本文翻譯自 《Laravel - My first framework》

從 CURD 到 排序 和 過濾，Query Builder 提供了方便的操作符來處理資料庫中的資料。這些操作符大多數可以組合在一起，以充分利用單個查詢。

Laravel 一般使用 DB facade 來進行資料庫查詢。當我們執行 DB 的「命令」(、或者說「操作符」)時，Query Builder 會構建一個 SQL 查詢，該查詢將根據 table() 方法中指定的表執行查詢。

該查詢將使用 app/config/database.php 檔案中指定的資料庫連線執行。 查詢執行的結果將返回：檢索到的記錄、布林值或一個空結果集。

下表中是 Query Builder 的常用操作符：

操作符	描述
insert(array(...))	接收包含欄位名和值的陣列，插入資料至資料庫
find($id)	檢索一個主鍵 id 等於給定引數的記錄
update(array(...))	接收含有欄位名和值的陣列，更新已存在的記錄
delete()	刪除一條記錄
get()	返回一個 Illuminate\Support\Collection 結果，其中每個結果都是一個 PHP StdClass 物件的例項，例項中包含每行記錄中的列名及其值
take($number)	限制查詢結果數量

接下來，將講解 Query Builder 的各種操作。

Inserting records - 插入

insert 操作符將新行（記錄）插入到現有表中。我們可以通過提供資料陣列作為 insert 運算子的引數來指定要插入到表中的資料。

假設有一個 orders 表：

Key	Column	Type
primary	id	int (11), auto-incrementing
	price	int (11)
	product	varchar(255)

插入單行資料

把資料陣列傳給 insert 操作符，來告訴 Query Builder 插入新行：

DB::table('orders')->insert(
    [
        'price' => 200, // 設定 price 欄位值
        'product' => 'Console', // 設定 product 欄位值
    ]
);

Query Builder將 insert 命令轉換為特定於 database.php 配置檔案中指定的資料庫的 SQL 查詢。 作為引數傳遞給 insert 命令的資料將以引數的形式放入 SQL 查詢中。 然後，SQL 查詢將在指定的表 "orders" 上執行，執行結果將返回給呼叫者。
下圖說明了整個過程：

可以看到，Laravel 使用 PDO 來執行 SQL 語句。通過為資料新增佔位符來使用準備好的語句可以增強 SQL 注入的保護性，並增加資料插入和更新的安全性。

插入多行資料

Query Builder 的 insert 操作符同樣可用於插入多行資料。 傳遞一個包含陣列的陣列可以插入任意數量的行：

DB::table('orders')->insert(
    [
        ['price' => 400, 'product' => 'Laptop'],
        ['price' => 200, 'product' => 'Smartphone'],
        ['price' => 50, 'product' => 'Accessory'],
    ]
);

此 insert 語句將建立三個新記錄，Laravel 構建的 SQL 查詢是：

insertinto`orders`(`price`,`product`)values(?,?),(?,?),(?,?)

可以看到， Laravel 聰明地在一個查詢中插入三行資料，而不是執行三個單獨的查詢。

Retrieving records - 檢索

Query Builder 提供了多種從資料庫獲取資料的操作符，以靈活的適應許多不同的情況，例如：

• 檢索單個記錄
• 檢索表中的所有記錄
• 僅檢索表中所有記錄的特定列
• 檢索表中有限數量的記錄

檢索單個記錄

可以使用 find 操作符從表中檢索單個記錄。只需提供要檢索的記錄的主鍵的值作為 find 的引數，Laravel 將返回該記錄作為物件。如果未找到該記錄，則返回 NULL。

$order = DB::table('orders')->find(3); 

/*
object(stdClass)#157 (3) {
    ["id"]=>string(1) "3"
    ["price"]=>string(3) "200"
    ["product"]=>string(10) "Smartphone"
 }
 */

注意： find 操作符以 id 作為主鍵進行查詢，如想使用別的主鍵，請使用其它操作符。

Laravel 構建的 SQL 查詢是：

select * from `orders` where `id` = ? limit 1

檢索表中的所有記錄

要從表中檢索所有記錄，可以使用 get 操作符而不用任何引數。在指定的表上執行 get (前面沒有別的操作符) 將會將該表中的所有記錄作為物件陣列返回。

$orders = DB::table('orders')->get();

/*
array(4) { [0]=>
    object(stdClass)#157 (3) { 
        ["id"]=>string(1) "1"
        ["price"]=>string(3) "200"
        ["product"]=>string(7) "Console"
    }

    ... 3 more rows returned as objects ...
}
*/

Laravel 構建的 SQL 查詢是：

select * from `orders`

檢索僅包含特定列的所有記錄

將所需的列名作為引數陣列傳遞給 get 運算子，可獲得表中所有記錄的特定列。

$orders = DB::table('orders')->get(['id','price']);

/*
array(4) { [0]=>
    object(stdClass)#157 (2) {
        ["id"]=>string(1) "1"
        ["price"]=>string(3) "200"
    }
    ... 3 more rows returned as objects ... 
}
*/

Laravel 構建的 SQL 查詢是：

select `id`, `price` from `orders`

檢索表中有限數量的記錄

要指定要從表中獲取的最大記錄數，可以使用 take 操作符，並將 get 附加到查詢中。

$orders = DB::table('orders')->take(50)->get();

$orders 陣列中最多有 50 條資料。

Updating records - 更新

使用 Query Builder 更新記錄與建立新記錄非常相似。要更新現有記錄或一組記錄的資料，可以將操作符 update 附加到查詢中，並將一個新資料陣列作為引數傳遞給它。同時可以使用查詢鏈定位要更新的特定記錄。

更新特定記錄

使用 where 操作符來指定特定記錄並更新：

DB::table('orders')
    ->where('price','>','50')
    ->update(['price' => 100]);

Laravel 構建的 SQL 查詢是：

update `orders` set `price` = ? where `price` > ?

更新所有記錄

如果不限定條件直接使用 update ，將更新表中所有記錄：

DB::table('orders')->update(['product'=>'Headphones']);

Laravel 構建的 SQL 查詢是：

update `orders` set `product` = ?

Deleting records - 刪除

使用 Query Builder 從表中刪除記錄遵循與更新記錄相同的模式。 可以使用 delete 操作符刪除與某些條件匹配的特定記錄或刪除所有記錄。

刪除特定記錄

使用 where 操作符來指定要刪除的特定記錄：

DB::table('orders')
    ->where('product','=','Smartphone')
    ->delete();

Laravel 構建的 SQL 查詢是：

delete from `orders` where `product` = ?

在資料庫應用程式中管理資料時，往往需要對哪些記錄進行嚴格的控制。 這可能是要準確地獲得應用程式規範要求的資料集，或者只刪除符合某些條件的幾條記錄。 如果使用純 SQL ，其中一些操作可能會變得非常複雜。 Laravel 的 Query Builder 允許過濾，排序和分組資料，同時保持清晰一致的語法，易於理解。

下表中是 Query Builder 的常用的過濾、排序和分組操作符：

操作符	描述
where('column','comparator','value')	檢索符合條件的記錄
orderBy('column','order')	按指定的列和順序排序記錄（升序或降序）
groupBy('column')	按列分組

Query Chaining - 查詢鏈

查詢連結允許在單個查詢中執行多個資料庫操作。查詢鏈將可以與資料執行的各種動作的順序相互結合，以獲得可以操作的特定結果。通過各種引數過濾、排序資料等等可以表示為對錶中的資料執行的一系列操作：

Laravel 允許根據需要將多個查詢放在一起。查詢連結可以顯著減少編寫的程式碼量來執行復雜的資料庫操作。 例如，要對 users 表執行上述操作，可以將過濾和排序一起放入單個查詢鏈，如圖所示：

注意：可以使用查詢鏈來執行多個操作，如排序、過濾、分組，以精確定位可以進一步檢索、更新或刪除的一組資料。 但不能在單個查詢中將 insert/get/update/delete 操作混合在一起。

Where 操作符

Query Builder 的 Where 操作符提供了一個乾淨的介面，用於執行 SQL 的 WHERE 子句，並具有與之非常相似的語法。例如：

• 選擇符合特定標準的記錄
• 選擇符合任一條件的記錄
• 選擇具有特定值範圍的列的記錄
• 選擇列超出值範圍的記錄

使用 where 選擇記錄後，可以執行之前討論過的任何操作：檢索、更新或刪除。

簡單的 where 查詢

where 的查詢由提供用於過濾資料的三個引數組成：

• 用於比較的列名
• 用於比較的運算子
• 用於比較的值

如果僅將兩個引數傳遞給 where 操作符， Laravel 會預設使用 = 進行比較，可以減少程式碼量。

下表是常用的 where 比較運算子：

運算子	描述
=	等於
	小於
>	大於
	小於等於
>=	大於等於
或!=	不等於
like	模糊查詢
not like	模糊查詢

除了使用單個 where 操作符，還可可以連結多個 where 來進一步過濾結果。 Laravel 會在 SQL 語句中自動將 AND 連結在 where 操作符之間。

$users = DB::table('users')
            // Match users whose last_name column starts with “A” 
            ->where('last_name', 'like','A%')
            // Match users whose age is less than 50 
            ->where('age','<' ,50)
            // Retrieve the records as an array of objects 
            ->get();

Laravel 構建的 SQL 查詢是：

select * from `users` where `last_name` like ? and `age` < ?

orWhere

通過使用 orWhere 操作符可以選擇幾個匹配至少一個條件的資料。它具有與 where 操作符完全相同的語法，並且必須將其附加到現有的 where操作符，以使其執行。

$orders = DB::table('orders')
            // Match orders that have been marked as processed 
            ->where('processed', 1)
            // Match orders that have price lower than or equal to 250 
            ->orWhere('price','<=' ,250)
            // Delete records that match either criterion
            ->delete();

Laravel 構建的 SQL 查詢是：

delete from `orders` where `processed` = ? or `price` <= ?

whereBetween

whereBetween 方法用來驗證欄位的值介於兩個值之間。它只需要兩個引數，一個用於匹配的列和一個包含兩個數值的陣列，表示一個範圍。

$users = DB::table('users')
            // Match users that have the value of “credit” column between 100 and 300 
            ->whereBetween('credit', [100,300])
            // Retrieve records as an array of objects
            ->get();

Laravel 構建的 SQL 查詢是：

select * from `users` where `credit` between ? and ?

orderBy - 排序

Query Builder 的 orderBy 操作符提供了一種簡單的方法來對從資料庫檢索的資料進行排序。 orderBy 類似於 SQL 中的 ORDER BY 子句。要通過一些列對一組資料進行排序，需要將兩個引數傳遞給 orderBy ：排序資料的列和排序方向（升序或降序）。

將 orderBy 操作符應用於由 Query Builder 的一個操作符檢索的一組資料，將根據列名稱和指定的方向對資料進行排序。

$products = DB::table('products')
                // Sort the products by their price in ascending order 
                ->orderBy('price', 'asc')
                // Retrieve records as an array of objects
                ->get();

還可以使用查詢鏈來適應更復雜的過濾和排序方案。

$products = DB::table('products')
                // Get products whose name contains letters “so” 
                ->where('name','like','%so%')
                // Get products whose price is greater than 100 
                ->where('price','>', 100)
                // Sort products by their price in ascending order 
                ->orderBy('price', 'asc')
                // Retrieve products from the table as an array of objects 
                ->get();

像 where 一樣，orderBy 操作符是可連結的，可以組合多個 orderBy 以獲取需要實現的排序結果。

groupBy - 分組

可以使用類似於 SQL 中 GROUP BY 子句的 groupBy 操作符將記錄組合在一起。 它只接受一個引數：用於對記錄進行分組的列。

$products = DB::table('products')
            // Group products by the “name” column
            ->groupBy('name')
            // Retrieve products from the table as an array of objects 
            ->get();

JOIN - 聯結

Laravel 的 Query Builder 支援資料庫所有型別的 Join 語句。 聯結語句用於組合具有這些表共同值的多個表中的記錄。 例如有兩個表 users 和 orders ，其內容如圖所示：

雖然可以使用 Join 語句組合兩個以上的表，但是我們將僅使用圖中的兩個表來演示可以在 Query Builder 中使用的 Join 語句型別。

注意：如果聯結的表具有相同名稱的列，則應小心。 可以使用 select() 來代替重複的列。

Inner Join - 內聯結

Inner Join 是一種簡單而常見的 Join 型別。 它用於返回一個表中在另一個表中具有完全匹配條件的所有記錄。

可以使用查詢鏈組合多個 join 操作符，來聯結兩個以上的表。

// Use table “users” as first table
$usersOrders = DB::table('users')
    // Perform a Join with the “orders” table, checking for the presence of matching
    // “user_id” column in “orders” table and “id” column of the “user” table. 
    ->join('orders', 'users.id', '=', 'orders.user_id')
    // Retrieve users from the table as an array of objects containing users and
    // products that each user has purchased
    ->get();

Laravel 構建的 SQL 查詢是：

select * from `users` inner join `orders` on `users`.`id` = `orders`.`user_id`

下圖顯示了內聯結為兩組資料之間的陰影區域的結果。

Left Join - 左聯結

左連線比內連線更具包容性，並具有類似的語法。 它生成一組在兩個表之間匹配的記錄，另外它返回從第一個表中有而其它表中沒有匹配的所有記錄。 語法的唯一區別是使用 leftJoin 操作符而不是 join。

// Use table “users” as first table
$usersOrders = DB::table('users')
    // Perform a Left Join with the “orders” table, checking for the presence of
    // matching “user_id” column in “orders” table and “id” column of the “user” table. 
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    // Retrieve an array of objects containing records of “users” table that have
    // a corresponding record in the “orders” table and also all records in “users”
    // table that don’t have a match in the “orders” table
    ->get();

Laravel 構建的 SQL 查詢是：

select * from `users` left join `orders` on `users`.`id` = `orders`.`user_id`

此查詢將包含 users 中的所有行，而不管 orders 表中是否具有匹配的條目。結果列的值將與具有內聯結的值相同，但是來自 users 表中的那些不在 orders 中匹配的行將返回 id，item 和 user_id列。

其它型別的聯結

Laravel 的 Query Builder 非常靈活，可以考慮連線查詢的特殊情況，並允許執行資料庫支援的所有型別的連線查詢。 大多數SQL資料庫引擎 (如 MySQL 和 SQLite) 支援 內左、右聯結/外左、右聯結，其他 SQL 引擎 (如 Postgres 和SQL Server) 還支援完全聯結。
可以通過向 join 操作符提供第五個引數，指定要執行的 Join 查詢型別，來執行資料庫支援的任何型別的聯結。

下面顯示了與 Laravel 支援的所有資料庫引擎的 Join 型別：

// Right Join
join('orders', 'users.id', '=', 'orders.user_id','right') // Right Outer Join
join('orders', 'users.id', '=', 'orders.user_id','right outer')
// Excluding Right Outer Join
join('orders', 'users.id', '=', 'orders.user_id','right outer') ->where('orders.user_id',NULL)
// Left Join
join('orders', 'users.id', '=', 'orders.user_id','left') // Left Outer Join
join('orders', 'users.id', '=', 'orders.user_id','left outer')
// Excluding Left Outer Join
join('orders', 'users.id', '=', 'orders.user_id','left outer') ->where('orders.user_id',NULL)
// Cross join
join('orders', 'users.id', '=', 'orders.user_id','cross')

以上就是 Laravel Query Builder 的介紹，下一篇文章中將講解 Laravel Eloquent 的用法。