查询构建器

引言

Laravel 的数据库查询构建器提供了一个方便、流畅的接口，用于创建和执行数据库查询。它几乎可以用于应用中的所有数据库操作，并且与 Laravel 支持的所有数据库系统完全兼容。

Laravel 查询构建器使用 PDO 参数绑定来保护应用免受 SQL 注入攻击。传入查询构建器的绑定参数无需进行清理或转义。

PDO 不支持绑定列名。因此，绝不应允许用户输入决定查询中引用的列名，包括 ORDER BY 所使用的列。

执行数据库查询

从表中检索所有行

你可以使用 DB 门面的 table 方法来开始一个查询。table 方法会返回指定表的流式查询构建器实例，这样你就可以在查询上链式添加更多约束，最后使用 get 方法获取查询结果：

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use Illuminate\View\View;

class UserController extends Controller
{
    /**
     * 显示应用中所有用户的列表。
     */
    public function index(): View
    {
        $users = DB::table('users')->get();

        return view('user.index', ['users' => $users]);
    }
}

get 方法返回一个 Illuminate\Support\Collection 实例，其中包含查询结果，每个结果都是 PHP 的 stdClass 对象。你可以通过对象属性访问每列的值：

use Illuminate\Support\Facades\DB;

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

foreach ($users as $user) {
    echo $user->name;
}

Laravel 的集合提供了丰富且强大的方法用于数据映射和归约。有关更多信息，请参考集合文档。

从表中检索单行/单列

如果你只需要从数据库表中检索单行数据，可以使用 DB 门面的 first 方法。该方法会返回一个 stdClass 对象：

$user = DB::table('users')->where('name', 'John')->first();

return $user->email;

如果你希望在没有匹配行时抛出 Illuminate\Database\RecordNotFoundException 异常，可以使用 firstOrFail 方法。如果异常未被捕获，Laravel 会自动返回 HTTP 404 响应：

$user = DB::table('users')->where('name', 'John')->firstOrFail();

如果你只需要单个值，而不是整行记录，可以使用 value 方法直接获取指定列的值：

$email = DB::table('users')->where('name', 'John')->value('email');

通过 id 列的值检索单行记录，可以使用 find 方法：

$user = DB::table('users')->find(3);

检索某列的值列表

如果你想获取某列的值集合，可以使用 pluck 方法。例如，检索所有用户的 title 列：

use Illuminate\Support\Facades\DB;

$titles = DB::table('users')->pluck('title');

foreach ($titles as $title) {
    echo $title;
}

你可以通过为 pluck 方法提供第二个参数，指定集合使用哪一列作为键：

$titles = DB::table('users')->pluck('title', 'name');

foreach ($titles as $name => $title) {
    echo $title;
}

分块处理结果

如果你需要处理数千条数据库记录，可以考虑使用 DB 门面的 chunk 方法。该方法会一次检索一小块结果，并将每块结果传入闭包进行处理。例如，我们可以每次检索 100 条记录来遍历整个 users 表：

use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
    foreach ($users as $user) {
        // ...
    }
});

你可以通过在闭包中返回 false 来停止后续块的处理：

DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
    // 处理记录...

    return false;
});

如果你在分块处理时需要更新数据库记录，块结果可能会出现意外变化。此时，最好使用 chunkById 方法。该方法会根据记录的主键自动分页结果：

DB::table('users')->where('active', false)
    ->chunkById(100, function (Collection $users) {
        foreach ($users as $user) {
            DB::table('users')
                ->where('id', $user->id)
                ->update(['active' => true]);
        }
    });

由于 chunkById 和 lazyById 方法会向执行的查询中添加自己的 where 条件，通常你应当将自己的条件逻辑性地封装在闭包中：

DB::table('users')->where(function ($query) {
    $query->where('credits', 1)->orWhere('credits', 2);
})->chunkById(100, function (Collection $users) {
    foreach ($users as $user) {
        DB::table('users')
            ->where('id', $user->id)
            ->update(['credits' => 3]);
    }
});

需要注意的是，当你在分块回调中更新或删除记录时，任何对主键或外键的更改都可能影响块查询，从而导致某些记录未被包含在分块结果中。

延迟流式处理结果

lazy 方法的工作方式与 chunk 方法类似，都是将查询分块执行。但不同的是，lazy() 方法不会将每块结果传入回调，而是返回一个 LazyCollection，让你可以像处理单个流一样与结果进行交互：

use Illuminate\Support\Facades\DB;

DB::table('users')->orderBy('id')->lazy()->each(function (object $user) {
    // ...
});

同样地，如果你计划在迭代过程中更新检索到的记录，最好使用 lazyById 或 lazyByIdDesc 方法。这些方法会根据记录的主键自动分页结果：

DB::table('users')->where('active', false)
    ->lazyById()->each(function (object $user) {
        DB::table('users')
            ->where('id', $user->id)
            ->update(['active' => true]);
    });

需要注意的是，当在迭代过程中更新或删除记录时，对主键或外键的任何更改都可能影响查询结果，从而导致某些记录未被包含在最终结果中。

聚合函数

查询构建器还提供了多种方法来获取聚合值，如 count、max、min、avg 和 sum。你可以在构建查询后调用这些方法：

use Illuminate\Support\Facades\DB;

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

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

当然，你也可以将这些方法与其他条件子句结合使用，以更精确地计算聚合值：

$price = DB::table('orders')
    ->where('finalized', 1)
    ->avg('price');

判断记录是否存在

与其使用 count 方法来判断是否存在匹配查询条件的记录，你可以使用 exists 和 doesntExist 方法：

if (DB::table('orders')->where('finalized', 1)->exists()) {
    // ...
}

if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
    // ...
}

SELECT 查询

指定 Select 子句

你不一定总是想从数据库表中查询所有列。使用 select 方法，你可以为查询指定自定义的 “select” 子句：

use Illuminate\Support\Facades\DB;

$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 facade 提供的 raw 方法：

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

原始语句会直接注入到查询中，因此需要格外小心，避免产生 SQL 注入漏洞。

原始方法（Raw Methods）

除了使用 DB::raw 方法，你还可以使用以下方法在查询的不同部分插入原始表达式。请记住，Laravel 无法保证使用原始表达式的查询完全防止 SQL 注入。

selectRaw

selectRaw 方法可以替代 addSelect(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();

groupByRaw

groupByRaw 方法可用于为 group by 子句提供原始字符串：

$orders = DB::table('orders')
    ->select('city', 'state')
    ->groupByRaw('city, state')
    ->get();

连接查询（Joins）

内连接（Inner Join）

查询构建器也可用于向查询中添加 join 子句。要执行基本的“内连接”，可以在查询构建器实例上使用 join 方法。传递给 join 方法的第一个参数是要连接的表名，其余参数指定连接的列约束。你甚至可以在单个查询中连接多个表：

use Illuminate\Support\Facades\DB;

$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 / Right 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 方法可以执行“交叉连接”，它会生成两个表的笛卡尔积：

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

高级连接（Advanced Join）

你还可以定义更复杂的连接条件。通过向 join 方法传递闭包（closure），可以获得一个 Illuminate\Database\Query\JoinClause 实例，从而为 join 子句指定约束：

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

如果希望在连接中使用 where 条件，可以使用 JoinClause 实例提供的 where 或 orWhere 方法，这些方法会将列与具体值进行比较，而非列与列：

DB::table('users')
    ->join('contacts', function (JoinClause $join) {
        $join->on('users.id', '=', 'contacts.user_id')
            ->where('contacts.user_id', '>', 5);
    })
    ->get();

子查询连接（Subquery Joins）

可以使用 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 (JoinClause $join) {
        $join->on('users.id', '=', 'latest_posts.user_id');
    })->get();

侧向连接（Lateral Joins）

侧向连接目前支持 PostgreSQL、MySQL >= 8.0.14 和 SQL Server。

使用 joinLateral 和 leftJoinLateral 方法可以对子查询执行“侧向连接”。方法接受两个参数：子查询和其别名，连接条件应在子查询的 where 子句中指定。侧向连接会针对每一行进行评估，并可引用子查询外的列。

例如，我们希望获取每个用户及其最近三篇博客：

$latestPosts = DB::table('posts')
    ->select('id as post_id', 'title as post_title', 'created_at as post_created_at')
    ->whereColumn('user_id', 'users.id')
    ->orderBy('created_at', 'desc')
    ->limit(3);

$users = DB::table('users')
    ->joinLateral($latestPosts, 'latest_posts')
    ->get();

联合查询（Unions）

查询构建器还提供了一个便捷的方法来将两个或多个查询“合并（union）”在一起。例如，你可以先创建一个初始查询，然后使用 union 方法将其与其他查询合并：

use Illuminate\Support\Facades\DB;

$first = DB::table('users')
    ->whereNull('first_name');

$users = DB::table('users')
    ->whereNull('last_name')
    ->union($first)
    ->get();

除了 union 方法外，查询构建器还提供了 unionAll 方法。使用 unionAll 合并的查询不会去除重复结果。unionAll 方法的签名与 union 方法相同。

基础 WHERE 子句

WHERE 子句

你可以使用查询构建器的 where 方法向查询添加 “where” 条件。最基本的 where 调用需要三个参数：第一个参数是列名，第二个参数是操作符（可以是数据库支持的任意操作符），第三个参数是用于与列值比较的值。

例如，下面的查询会检索 votes 列等于 100 且 age 列大于 35 的用户：

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

为了方便，如果你只是想判断某列是否等于某个值，可以将该值作为 where 方法的第二个参数，Laravel 会默认使用 = 操作符：

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

你也可以传递一个关联数组给 where 方法，以便快速对多列进行查询：

$users = DB::table('users')->where([
    'first_name' => 'Jane',
    'last_name' => 'Doe',
])->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 方法传递一个条件数组。数组的每个元素应为一个三元素数组，对应 where 方法的三个参数：

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

需要注意，PDO 不支持绑定列名，因此绝不可让用户输入决定查询中使用的列名，包括 order by 列。

另外，MySQL 和 MariaDB 在字符串与数字比较时会自动将字符串类型转换为整数，其中非数字字符串会被转换为 0，这可能导致意外结果。例如，如果表中 secret 列的值为 "aaa"，执行 User::where('secret', 0) 时，该行也会被返回。为避免这种情况，确保在查询前将所有值转换为合适的数据类型。

OR WHERE 子句

在链式调用查询构建器的 where 方法时，这些 “where” 条件会默认通过 AND 操作符连接。但你也可以使用 orWhere 方法，将条件通过 OR 操作符连接。orWhere 方法接受的参数与 where 方法相同：

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

如果你需要将 OR 条件用括号括起来，可以将一个闭包传递给 orWhere 方法的第一个参数：

use Illuminate\Database\Query\Builder;

$users = DB::table('users')
    ->where('votes', '>', 100)
    ->orWhere(function (Builder $query) {
        $query->where('name', 'Abigail')
              ->where('votes', '>', 50);
    })
    ->get();

上面的例子会生成如下 SQL：

select * from users where votes > 100 or (name = 'Abigail' and votes > 50)

当使用全局作用域（global scopes）时，务必要对 orWhere 调用进行分组，以避免出现意外行为。

WHERE NOT 子句

whereNot 和 orWhereNot 方法可用于对一组查询条件取反。例如，下面的查询会排除处于清仓状态或价格低于 10 的商品：

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

快速入门

分页