Laravel 文件閱讀：資料庫之查詢語句構造器（上篇）

zhangbao發表於2017-08-26
Laravel資料庫

翻譯、衍生自：https://learnku.com/docs/laravel/5.4/queries

簡介

Laravel 的查詢構造器（query builder）提供流暢的介面，幫助你構造、執行資料庫查詢。這裡的「查詢」，並不只是指select 語句，還有 update、delete 和 insert 語句等。在所有支援的資料庫系統中都執行良好。

Laravel 的查詢構造器使用 PDO 引數繫結保護程式免受 SQL 注入攻擊，所以你傳遞的繫結引數無需進行清理操作了。

查詢資料

獲得表格裡所有資料

使用 DB 門面的 table 方法開啟查詢。table 方法返回給定表格的查詢構造器例項，允許你用鏈式呼叫的方式為查詢新增約束條件，最後使用 get 方法獲得結果。

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
    /**
     * Show a list of all of the application's users.
     *
     * @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) {
        //
    }
});

也可以通過在閉包裡 return false 停止繼續分塊輸出資料。

DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    // Process the records...

    return false;
});

聚合

查詢構造器也提供了許多聚合方法：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');

Select

有時，並不需要獲得資料表所有欄位的資料，而是選擇性的獲得指定幾個欄位的資料，這時可以使用 select 方法：

$users = DB::table('users')->select('name', 'email as user_email')->get();

distinct 方法用於對結果去重：

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

如果當前已經有了一個查詢語句構造器例項，想在此基礎上新增一個 select 子句，那麼就要用 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();

Joins

內連線

查詢語句構造器也可以用來寫連線語句（join statements）。「內連線」就是用 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();

左連線

左連線使用 leftJoin 方法，它的方法簽名和 join 是一樣的：

$user = DB::table('users')
                    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
                    ->get();

交叉連線

交叉連線使用 crossJoin 方法。

$users = DB::table('sizes')
                    ->crossJoin('colours')
                    ->get();

高階連線子句

如果要使用更加高階的連線子句，就要通過 join 方法的第二個引數，它是一個閉包。閉包接收一個 JoinClause 物件，用於對 join子句新增約束條件：

DB::table('users')
            ->join('contatcs', function ($join) {
                $join->on('users.id', '=', 'contatcs.user_id')->orOn(...);
            })
            ->get();

你還可以在 join 子句上使用「where」 約束，得用到 where 或者 orWhere 方法——限定列的取值範圍：

DB::table('users')
            ->join('contatcs', function ($join) {
                $join->on('users.id', '=', 'contatcs.user_id')
                         ->where('contacts.user_id', '>', 5);
            })
            ->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 方法最基本的用法是給它傳遞 3 個引數。第一個引數是欄位名，第二個引數是操作符（資料庫支援的任何操作符），第三個引數是欄位值。

例如，下面的查詢查出所有「投票」數是 100 的記錄：

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

為了方便，當驗證某個欄位是否等於某個值時，可以省略中間的 =。

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

除了 = ，這裡還有使用其他操作符的例子：

$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 子句

使用 orWhere 方法新增 or 子句約束。orWhere 的方法簽名和 where 是一樣的：

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

其他子句

whereBetween

whereBetween 方法驗證欄位值是否在給定範圍之間。

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

whereBetween 方法驗證欄位值是否在給定範圍之外。

$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

whereDate 用來比較欄位值是否滿足給定的日期。

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

whereMonth 用來比較欄位值是否滿足給定的月份。

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

whereDay 用來比較欄位值是否滿足給定的日期。

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

whereYear 用來比較欄位值是否滿足給定的年份。

$users = DB::table('users')
                ->whereYear('created_at', '2016')
                ->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 的查詢語句構造器照樣 OK。看個例子：

DB::table('users')
            ->where('name', '=', 'John')
            ->orWhere(function ($query) {
                $query->where('votes', '>', 100)
                      ->where('title', '<>', 'Admin');
            })
            ->get();

當傳遞 Closure 給 orWhere 方法的時候，就表示開始一個分組約束了。 Closure 接收一個查詢例項用來在圓括號（()）內設定限制。上面的例子會產生下面的 SQL：

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

Where Exists 子句

whereExists 方法用來寫 where exists SQL 子句。whereExists 方法接收一個閉包，包含一個查詢語句構造器例項，用於定義「where」子句裡的內容：

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 和 Postgres 都支援。查詢 JSON 欄位，使用 -> 操作符：

$users = DB::table('users')
                ->where('options->language', 'en')
                ->get();

$users = DB::table('users')
                ->where('preferences->dining->meal', 'salad')
                ->get();
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章