Database: Query Builder

Introduction
Running Database Queries
Select Statements
Raw Expressions
Joins
Unions
Basic Where Clauses
Advanced Where Clauses
Ordering, Grouping, Limit and Offset
Conditional Clauses
Insert Statements
- Upserts
Update Statements
- Updating JSON Columns
- Increment and Decrement
Delete Statements
Pessimistic Locking
Reusable Query Components
Debugging

Introduction

Laravel's database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application and works perfectly with all of Laravel's supported database systems.

The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks. There is no need to clean or sanitize strings passed to the query builder as query bindings.

[!WARNING]
PDO does not support binding column names. Therefore, you should never allow user input to dictate the column names referenced by your queries, including "order by" columns.

运行数据库查询

从表中检索所有记录

可以使用 DB facade 提供的 table 方法开始查询。table 方法为指定表返回链式查询构造器实例，允许在查询上链接更多约束，最后使用 get 方法检索查询结果：

<?php

namespace App\Http\Controllers;

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

class UserController extends Controller
{
    /**
     * Show a list of all of the application's users.
     */
    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 集合提供了各种及其强大的方法来映射和裁切数据。有关 Laravel 集合的更多信息，[查看集合文档 ]((learnku.com/docs/laravel/12.x/coll... "查看集合文档 ")。

从表中检索单行或单列

如果只需要从数据库表中检索单行，可以使用 DB facade 中的 first 方法。此方法将返回单个 stdClass 对象：

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

return $user->email;

vaexm 翻译于

由 gyp719 审阅

如果您想从数据库表中检索单行数据，但在找不到匹配行时抛出 Illuminate\Database\RecordNotFoundException 异常，可以使用 firstOrFail 方法。如果未捕获 RecordNotFoundException 异常，系统将自动向客户端返回 404 HTTP 响应：

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

获取某一列的值列表

如果要检索包含单个列值的 Illuminate\Support\Collection 实例，则可以使用 pluck 方法。在此示例中，将检索 user 表中 title 的集合：

use Illuminate\Support\Facades\DB;

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

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

可以通过向 pluck 方法提供第二个参数来指定结果集中应该用作 key 的列：

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

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

分块结果

如果需要处理数千条数据库记录，可以考虑使用 DB facade 提供的 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) {
        // ...
    }
});

vaexm 翻译于

由 gyp719 审阅

可以通过从闭包中返回 false 来停止处理其余的块：

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

    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) {
    // ...
});

vaexm 翻译于

由 gyp719 审阅

Once again, if you plan to update the retrieved records while iterating over them, it is best to use the lazyById or lazyByIdDesc methods instead. These methods will automatically paginate the results based on the record's primary key:

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

[!WARNING]
When updating or deleting records while iterating over them, any changes to the primary key or foreign keys could affect the chunk query. This could potentially result in records not being included in the results.

Aggregates

The query builder also provides a variety of methods for retrieving aggregate values like count, max, min, avg, and sum. You may call any of these methods after constructing your query:

use Illuminate\Support\Facades\DB;

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

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

Of course, you may combine these methods with other clauses to fine-tune how your aggregate value is calculated:

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

Determining if Records Exist

Instead of using the count method to determine if any records exist that match your query's constraints, you may use the exists and doesntExist methods:

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

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

Select Statements

Specifying a Select Clause

You may not always want to select all columns from a database table. Using the select method, you can specify a custom "select" clause for the query:

use Illuminate\Support\Facades\DB;

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

The distinct method allows you to force the query to return distinct results:

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

If you already have a query builder instance and you wish to add a column to its existing select clause, you may use the addSelect method:

$query = DB::table('users')->select('name');

$users = $query->addSelect('age')->get();

Raw Expressions

Sometimes you may need to insert an arbitrary string into a query. To create a raw string expression, you may use the raw method provided by the DB facade:

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

[!WARNING]
Raw statements will be injected into the query as strings, so you should be extremely careful to avoid creating SQL injection vulnerabilities.

Raw Methods

Instead of using the DB::raw method, you may also use the following methods to insert a raw expression into various parts of your query. Remember, Laravel cannot guarantee that any query using raw expressions is protected against SQL injection vulnerabilities.

`selectRaw`

The selectRaw method can be used in place of addSelect(DB::raw(/* ... */)). This method accepts an optional array of bindings as its second argument:

$orders = DB::table('orders')
    ->selectRaw('price * ? as price_with_tax', [1.0825])
    ->get();

`whereRaw / orWhereRaw`

The whereRaw and orWhereRaw methods can be used to inject a raw "where" clause into your query. These methods accept an optional array of bindings as their second argument:

$orders = DB::table('orders')
    ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
    ->get();

`havingRaw / orHavingRaw`

The havingRaw and orHavingRaw methods may be used to provide a raw string as the value of the "having" clause. These methods accept an optional array of bindings as their second argument:

$orders = DB::table('orders')
    ->select('department', DB::raw('SUM(price) as total_sales'))
    ->groupBy('department')
    ->havingRaw('SUM(price) > ?', [2500])
    ->get();

`orderByRaw`

The orderByRaw method may be used to provide a raw string as the value of the "order by" clause:

$orders = DB::table('orders')
    ->orderByRaw('updated_at - created_at DESC')
    ->get();

`groupByRaw`

The groupByRaw method may be used to provide a raw string as the value of the group by clause:

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

Joins

Inner Join Clause

The query builder may also be used to add join clauses to your queries. To perform a basic "inner join", you may use the join method on a query builder instance. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. You may even join multiple tables in a single query:

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

如果你想使用「left join」或者「right join」代替「inner 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();

交叉连接子句

你可以使用 crossJoin 方法来执行「交叉连接」。交叉连接会在第一个表和连接的表之间生成笛卡尔积：

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

高级连接子句

你也可以指定更高级的连接子句。为此，可以将一个闭包作为 join 方法的第二个参数传递。该闭包将接收到一个 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();

vaexm 翻译于

由 gyp719 审阅

Subquery Joins

You may use the joinSub, leftJoinSub, and rightJoinSub methods to join a query to a subquery. Each of these methods receives three arguments: the subquery, its table alias, and a closure that defines the related columns. In this example, we will retrieve a collection of users where each user record also contains the created_at timestamp of the user's most recently published blog post:

$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

[!WARNING]
Lateral joins are currently supported by PostgreSQL, MySQL >= 8.0.14, and SQL Server.

You may use the joinLateral and leftJoinLateral methods to perform a "lateral join" with a subquery. Each of these methods receives two arguments: the subquery and its table alias. The join condition(s) should be specified within the where clause of the given subquery. Lateral joins are evaluated for each row and can reference columns outside the subquery.

In this example, we will retrieve a collection of users as well as the user's three most recent blog posts. Each user can produce up to three rows in the result set: one for each of their most recent blog posts. The join condition is specified with a whereColumn clause within the subquery, referencing the current user row:

$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

The query builder also provides a convenient method to "union" two or more queries together. For example, you may create an initial query and use the union method to union it with more queries:

use Illuminate\Support\Facades\DB;

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

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

In addition to the union method, the query builder provides a unionAll method. Queries that are combined using the unionAll method will not have their duplicate results removed. The unionAll method has the same method signature as the union method.

Basic Where Clauses

Where Clauses

You may use the query builder's where method to add "where" clauses to the query. The most basic call to the where method requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. The third argument is the value to compare against the column's value.

For example, the following query retrieves users where the value of the votes column is equal to 100 and the value of the age column is greater than 35:

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

For convenience, if you want to verify that a column is = to a given value, you may pass the value as the second argument to the where method. Laravel will assume you would like to use the = operator:

$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 函数。数组的每个元素应该是一个包含通常传递给 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();

vaexm 翻译于

由 gyp719 审阅

如果你需要在括号内分组一个「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)

[!警告]
你应始终对 orWhere 调用进行分组，以避免在应用全局作用域时出现意外行为。

Where Not 子句

whereNot 和 orWhereNot 方法可用于否定一组给定的查询约束条件。例如，以下查询排除正在清仓的产品或价格低于 10 的产品：

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

Where Any / All / None 子句

有时你可能需要对多列采用相同的查询约束。例如，你可能想要检索给定列表中任何列与给定值 LIKE 匹配的所有记录。你可以使用 whereAny 方法来实现这一点：

$users = DB::table('users')
    ->where('active', true)
    ->whereAny([
        'name',
        'email',
        'phone',
    ], 'like', 'Example%')
    ->get();

上面的查询将生成以下 SQL：

SELECT *
FROM users
WHERE active = true AND (
    name LIKE 'Example%' OR
    email LIKE 'Example%' OR
    phone LIKE 'Example%'
)

vaexm 翻译于

由 gyp719 审阅

Similarly, the whereAll method may be used to retrieve records where all of the given columns match a given constraint:

$posts = DB::table('posts')
    ->where('published', true)
    ->whereAll([
        'title',
        'content',
    ], 'like', '%Laravel%')
    ->get();

The query above will result in the following SQL:

SELECT *
FROM posts
WHERE published = true AND (
    title LIKE '%Laravel%' AND
    content LIKE '%Laravel%'
)

The whereNone method may be used to retrieve records where none of the given columns match a given constraint:

$posts = DB::table('albums')
    ->where('published', true)
    ->whereNone([
        'title',
        'lyrics',
        'tags',
    ], 'like', '%explicit%')
    ->get();

The query above will result in the following SQL:

SELECT *
FROM albums
WHERE published = true AND NOT (
    title LIKE '%explicit%' OR
    lyrics LIKE '%explicit%' OR
    tags LIKE '%explicit%'
)

JSON Where Clauses

Laravel also supports querying JSON column types on databases that provide support for JSON column types. Currently, this includes MariaDB 10.3+, MySQL 8.0+, PostgreSQL 12.0+, SQL Server 2017+, and SQLite 3.39.0+. To query a JSON column, use the -> operator:

$users = DB::table('users')
    ->where('preferences->dining->meal', 'salad')
    ->get();

You may use whereJsonContains to query JSON arrays:

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

If your application uses the MariaDB, MySQL, or PostgreSQL databases, you may pass an array of values to the whereJsonContains method:

$users = DB::table('users')
    ->whereJsonContains('options->languages', ['en', 'de'])
    ->get();

You may use whereJsonLength method to query JSON arrays by their length:

$users = DB::table('users')
    ->whereJsonLength('options->languages', 0)
    ->get();

$users = DB::table('users')
    ->whereJsonLength('options->languages', '>', 1)
    ->get();

附加 Where 子句

whereLike / orWhereLike / whereNotLike / orWhereNotLike

whereLike 方法允许你在查询中添加「LIKE」子句以进行模式匹配。这些方法提供了一种与数据库无关的字符串匹配查询方式，并支持切换大小写敏感选项。默认情况下，字符串匹配是大小写不敏感的：

$users = DB::table('users')
    ->whereLike('name', '%John%')
    ->get();

您可以通过 caseSensitive 参数启用区分大小写的搜索：

$users = DB::table('users')
    ->whereLike('name', '%John%', caseSensitive: true)
    ->get();

orWhereLike 方法允许您添加带有 LIKE 条件的「or」子句：

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

whereNotLike 方法允许您向查询中添加「NOT LIKE」子句。

$users = DB::table('users')
    ->whereNotLike('name', '%John%')
    ->get();

同样地，你可以使用 orWhereNotLike 来添加一个带有 NOT LIKE 条件的「or」子句。

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

[!警告 ]
whereLike 区分大小写的搜索选项目前在 SQL Server 上不受支持。

whereIn / whereNotIn / orWhereIn / orWhereNotIn

whereIn 方法验证给定列的值是否包含在给定的数组中：

$users = DB::table('users')
    ->whereIn('id', [1, 2, 3])
    ->get();

whereNotIn 方法验证给定列的值是否不包含在给定的数组中：

$users = DB::table('users')
    ->whereNotIn('id', [1, 2, 3])
    ->get();

你也可以将一个查询对象作为 `whereIn1 方法的第二个参数：

$activeUsers = DB::table('users')->select('id')->where('is_active', 1);

$users = DB::table('comments')
    ->whereIn('user_id', $activeUsers)
    ->get();

vaexm 翻译于

由 gyp719 审阅

上述示例将生成以下 SQL：

select * from comments where user_id in (
    select id
    from users
    where is_active = 1
)

[!警告 ]
如果你向查询中添加大量整数绑定，可以使用 whereIntegerInRaw 或 whereIntegerNotInRaw 方法来大大减少内存使用。

whereBetween / orWhereBetween

whereBetween 方法验证一个列的值是否在两个值之间：

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

whereNotBetween / orWhereNotBetween

whereNotBetween 方法验证一个列的值是否在两个值之外：

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

whereBetweenColumns / whereNotBetweenColumns / orWhereBetweenColumns / orWhereNotBetweenColumns

whereBetweenColumns 方法验证一个列的值是否在同一表行中两个列的两个值之间：

$patients = DB::table('patients')
    ->whereBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
    ->get();

whereNotBetweenColumns 方法验证一个列的值是否在同一表行中两个列的两个值之外：

$patients = DB::table('patients')
    ->whereNotBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
    ->get();

whereNull / whereNotNull / orWhereNull / orWhereNotNull

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', '2016-12-31')
    ->get();

vaexm 翻译于

由 gyp719 审阅

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();

whereTime 方法可用于将列的值与特定时间进行比较：

$users = DB::table('users')
    ->whereTime('created_at', '=', '11:20:45')
    ->get();

wherePast / whereFuture / whereToday / whereBeforeToday / whereAfterToday

wherePast 和 whereFuture 方法可用于判断某列的值是否属于过去或未来：

$invoices = DB::table('invoices')
    ->wherePast('due_at')
    ->get();

$invoices = DB::table('invoices')
    ->whereFuture('due_at')
    ->get();

whereNowOrPast 和 whereNowOrFuture 方法可用于判断某列的值是否属于过去或将来（包含当前日期和时间）：

$invoices = DB::table('invoices')
    ->whereNowOrPast('due_at')
    ->get();

$invoices = DB::table('invoices')
    ->whereNowOrFuture('due_at')
    ->get();

whereToday、whereBeforeToday 和 whereAfterToday方法可用于分别判断某列的值是否为今天、早于今天或晚于今天：

$invoices = DB::table('invoices')
    ->whereToday('due_at')
    ->get();

$invoices = DB::table('invoices')
    ->whereBeforeToday('due_at')
    ->get();

$invoices = DB::table('invoices')
    ->whereAfterToday('due_at')
    ->get();

vaexm 翻译于

由 dkp 审阅

类似地，whereTodayOrBefore 和 whereTodayOrAfter 方法可用于判断列的值是否为今天之前或今天之后（包含今天的日期）：

$invoices = DB::table('invoices')
    ->whereTodayOrBefore('due_at')
    ->get();

$invoices = DB::table('invoices')
    ->whereTodayOrAfter('due_at')
    ->get();

whereColumn / orWhereColumn

whereColumn 方法可用于验证两列是否相等：

$users = DB::table('users')
    ->whereColumn('first_name', 'last_name')
    ->get();

你也可以向 whereColumn 方法传递一个比较运算符：

$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」子句用括号分组，以实现查询所需的逻辑分组。实际上，通常应该始终将 orWhere 方法的调用放在括号内，以避免意外的查询行为。为此，您可以向 where 方法传递一个闭包：

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

如您所见，向 where 方法传递一个闭包会指示查询构建器开始一个约束组。该闭包将接收一个查询构建器实例，您可以用它来设置在括号分组内应包含的约束条件。上面的示例将生成如下 SQL 语句：

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

[!警告]
应当始终将 orWhere 调用进行分组，以避免应用全局作用域时出现意外行为。

vaexm 翻译于

由 gyp719 审阅

高级 Where 子句

Where Exists 子句

whereExists 方法允许你编写「where exists」 SQL 子句。whereExists 方法接受一个闭包，该闭包将接收到一个查询构建器实例，允许你定义应放置在「exists」子句内部的查询：

$users = DB::table('users')
    ->whereExists(function (Builder $query) {
        $query->select(DB::raw(1))
            ->from('orders')
            ->whereColumn('orders.user_id', 'users.id');
    })
    ->get();

或者，你可以将一个查询对象提供给 whereExists 方法，而不是闭包：

$orders = DB::table('orders')
    ->select(DB::raw(1))
    ->whereColumn('orders.user_id', 'users.id');

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

上述两个示例都将生成以下 SQL：

select * from users
where exists (
    select 1
    from orders
    where orders.user_id = users.id
)

子查询 Where 子句

有时你可能需要构建一个「where」子句，将子查询的结果与给定值进行比较。你可以通过向 where 方法传递一个闭包和一个值来实现这一点。例如，以下查询将检索拥有最近给定类型「membership」的所有用户；

use App\Models\User;
use Illuminate\Database\Query\Builder;

$users = User::where(function (Builder $query) {
    $query->select('type')
        ->from('membership')
        ->whereColumn('membership.user_id', 'users.id')
        ->orderByDesc('membership.start_date')
        ->limit(1);
}, 'Pro')->get();

或者，你可能需要构建一个「where」子句，将一列与子查询的结果进行比较。你可以通过向 where 方法传递一个列、操作符和闭包来实现这一点。例如，以下查询将检索所有金额小于平均值的收入记录；

use App\Models\Income;
use Illuminate\Database\Query\Builder;

$incomes = Income::where('amount', '<', function (Builder $query) {
    $query->selectRaw('avg(i.amount)')->from('incomes as i');
})->get();

vaexm 翻译于

由 gyp719 审阅

全文 Where 子句

[!警告]
全文 where 子句目前由 MariaDB，MySQL 和 PostgreSQL 支持。

whereFullText 和 orWhereFullText 方法可用于为具有全文索引的列添加全文「where」子句。这些方法将由 Laravel 转换成适合的 SQL 给底层数据库系统。例如，对于使用 MySQL 的应用程序，将生成一个 MATCH AGAINST 子句：

$users = DB::table('users')
    ->whereFullText('bio', 'web developer')
    ->get();

排序、分组、条数限制和偏移量

排序

`orderBy` 方法

orderBy 方法允许你按给定列对查询结果进行排序。orderBy 方法接受的第一个参数应该是你要排序的列，而第二个参数确定排序方向，可以是 asc 或 desc：

$users = DB::table('users')
    ->orderBy('name', 'desc')
    ->get();

要按多个列排序，你可以简单地按需多次调用 orderBy：

$users = DB::table('users')
    ->orderBy('name', 'desc')
    ->orderBy('email', 'asc')
    ->get();

latest 和 oldest 方法

latest 和 oldest 方法允许你按日期轻松排序结果。默认情况下，结果将按表的 created_at 列排序。或者，你可以传递要排序的列名：

$user = DB::table('users')
    ->latest()
    ->first();

vaexm 翻译于

由 gyp719 审阅

随机排序

inRandomOrder 方法可用于随机排序查询结果。例如，你可以使用此方法获取一个随机用户：

$randomUser = DB::table('users')
    ->inRandomOrder()
    ->first();

移除现有排序

reorder 方法移除已应用于查询的所有「order by」子句

$query = DB::table('users')->orderBy('name');

$unorderedUsers = $query->reorder()->get();

你可以在调用 reorder 方法时传递列和方向，以便移除所有现有的「order by」子句，并为查询应用一个全新的排序：

$query = DB::table('users')->orderBy('name');

$usersOrderedByEmail = $query->reorder('email', 'desc')->get();

分组

`groupBy` 和 `having` 方法

如你所料，groupBy 和 having 方法可用于对查询结果进行分组。having 方法的签名类似于 where 方法：

$users = DB::table('users')
    ->groupBy('account_id')
    ->having('account_id', '>', 100)
    ->get();

你可以使用 havingBetween 方法在给定范围内过滤结果：

$report = DB::table('orders')
    ->selectRaw('count(id) as number_of_orders, customer_id')
    ->groupBy('customer_id')
    ->havingBetween('number_of_orders', [5, 15])
    ->get();

你可以向 groupBy 方法传递多个参数，以按多个列进行分组：

$users = DB::table('users')
    ->groupBy('first_name', 'status')
    ->having('account_id', '>', 100)
    ->get();

vaexm 翻译于

由 gyp719 审阅

要构建更复杂的 having 语句，请参考 havingRaw 方法。

条数限制和偏移量

The `skip` 和 `take` 方法

你可以使用 skip 和 take 方法来限制从查询返回的结果数量，或者在查询中跳过给定数量的结果：

$users = DB::table('users')->skip(10)->take(5)->get();

你可以使用 limit 和 offset 方法作为替代方案。这两个方法在功能上分别等同于 take 和 skip 方法：

$users = DB::table('users')
    ->offset(10)
    ->limit(5)
    ->get();

前提子句

有时你可能希望根据另一个条件将某些查询子句应用于查询。例如，如果给定的输入值存在于传入的 HTTP 请求中，你可能只想应用一个 where 语句。你可以使用 when 方法来实现这一点：

$role = $request->input('role');

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

when 方法仅在第一个参数为 true 时执行给定的闭包。如果第一个参数为 false，闭包将不会执行。因此，在上面的示例中，传递给 when 方法的闭包仅在 role 字段存在于传入请求中且评估为 true 时才会被调用。

你可以将另一个闭包作为 when 方法的第三个参数传递。这个闭包仅在第一个参数评估为 false 时才会执行。为了说明如何使用此功能，我们将使用它来配置查询的默认排序：

$sortByVotes = $request->boolean('sort_by_votes');

$users = DB::table('users')
    ->when($sortByVotes, function (Builder $query, bool $sortByVotes) {
        $query->orderBy('votes');
    }, function (Builder $query) {
        $query->orderBy('name');
    })
    ->get();

vaexm 翻译于

由 gyp719 审阅

插入语句

查询构建器还提供了一个 insert 方法，可用于将记录插入数据库表中。insert 方法接受一个列名和值的数组：

DB::table('users')->insert([
    'email' => 'kayla@example.com',
    'votes' => 0
]);

你可以通过传入一个二维数组一次性插入多条记录，其中每个子数组代表要插入到表中的一条记录:

DB::table('users')->insert([
    ['email' => 'picard@example.com', 'votes' => 0],
    ['email' => 'janeway@example.com', 'votes' => 0],
]);

insertOrIgnore 方法将在插入记录到数据库时忽略错误。使用此方法时，你应该注意，重复记录错误将被忽略，其他类型的错误也可能根据数据库引擎被忽略。例如，insertOrIgnore 将绕过 MySQL 的严格模式：

DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => 'sisko@example.com'],
    ['id' => 2, 'email' => 'archer@example.com'],
]);

当使用子查询确定应插入的数据时候，insertUsing 方法会将新记录插入进表中：

DB::table('pruned_users')->insertUsing([
    'id', 'name', 'email', 'email_verified_at'
], DB::table('users')->select(
    'id', 'name', 'email', 'email_verified_at'
)->where('updated_at', '<=', now()->subMonth()));

自增 ID

如果表有一个自增 ID，使用 insertGetId 方法插入记录并检索 ID：

$id = DB::table('users')->insertGetId(
    ['email' => 'john@example.com', 'votes' => 0]
);

[!警告]
当使用 PostgreSQL 时，insertGetId 方法期望自增列名为 id。如果你想从不同的「序列」中检索 ID，可以将列名作为 insertGetId 方法的第二个参数传递。

vaexm 翻译于

更新插入

upsert 方法将插入不存在的记录，并更新已存在的记录为指定的新值。方法的第一个参数包含要插入或更新的值，而第二个参数列出关联表中唯一标识记录的列。方法的第三个也是最后一个参数是一个列数组，如果数据库中已存在匹配记录，则应更新这些列：

DB::table('flights')->upsert(
    [
        ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
        ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
    ],
    ['departure', 'destination'],
    ['price']
);

在上面的示例中，Laravel 将尝试插入两条记录。如果记录已存在相同的 departure 和 destination 列值，Laravel 将更新该记录的 price 列。

[!警告]
除 SQL Server 外的所有数据库都要求 upsert 方法的第二个参数中的列具有「主键」或「唯一」索引。此外，MariaDB 和 MySQL 数据库驱动程序忽略 upsert 方法的第二个参数，并始终使用表的「主键」或「唯一」索引来检测现有记录。

更新语句

除了向数据库插入记录外，查询构建器还可以使用 update 方法更新现有记录。update 方法与 insert 方法类似，接受一个列和值对的数组，指示要更新的列。update 方法返回受影响的行数。你可以使用 where 子句约束 update 查询：

$affected = DB::table('users')
    ->where('id', 1)
    ->update(['votes' => 1]);

vaexm 翻译于

Update or Insert

Sometimes you may want to update an existing record in the database or create it if no matching record exists. In this scenario, the updateOrInsert method may be used. The updateOrInsert method accepts two arguments: an array of conditions by which to find the record, and an array of column and value pairs indicating the columns to be updated.

The updateOrInsert method will attempt to locate a matching database record using the first argument's column and value pairs. If the record exists, it will be updated with the values in the second argument. If the record cannot be found, a new record will be inserted with the merged attributes of both arguments:

DB::table('users')
    ->updateOrInsert(
        ['email' => 'john@example.com', 'name' => 'John'],
        ['votes' => '2']
    );

You may provide a closure to the updateOrInsert method to customize the attributes that are updated or inserted into the database based on the existence of a matching record:

DB::table('users')->updateOrInsert(
    ['user_id' => $user_id],
    fn ($exists) => $exists ? [
        'name' => $data['name'],
        'email' => $data['email'],
    ] : [
        'name' => $data['name'],
        'email' => $data['email'],
        'marketable' => true,
    ],
);

Updating JSON Columns

When updating a JSON column, you should use -> syntax to update the appropriate key in the JSON object. This operation is supported on MariaDB 10.3+, MySQL 5.7+, and PostgreSQL 9.5+:

$affected = DB::table('users')
    ->where('id', 1)
    ->update(['options->enabled' => true]);

Increment and Decrement

The query builder also provides convenient methods for incrementing or decrementing the value of a given column. Both of these methods accept at least one argument: the column to modify. A second argument may be provided to specify the amount by which the column should be incremented or decremented:

DB::table('users')->increment('votes');

DB::table('users')->increment('votes', 5);

DB::table('users')->decrement('votes');

DB::table('users')->decrement('votes', 5);

If needed, you may also specify additional columns to update during the increment or decrement operation:

DB::table('users')->increment('votes', 1, ['name' => 'John']);

In addition, you may increment or decrement multiple columns at once using the incrementEach and decrementEach methods:

DB::table('users')->incrementEach([
    'votes' => 5,
    'balance' => 100,
]);

Delete Statements

The query builder's delete method may be used to delete records from the table. The delete method returns the number of affected rows. You may constrain delete statements by adding "where" clauses before calling the delete method:

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

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

Pessimistic Locking

The query builder also includes a few functions to help you achieve "pessimistic locking" when executing your select statements. To execute a statement with a "shared lock", you may call the sharedLock method. A shared lock prevents the selected rows from being modified until your transaction is committed:

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

Alternatively, you may use the lockForUpdate method. A "for update" lock prevents the selected records from being modified or from being selected with another shared lock:

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

While not obligatory, it is recommended to wrap pessimistic locks within a transaction. This ensures that the data retrieved remains unaltered in the database until the entire operation completes. In case of a failure, the transaction will roll back any changes and release the locks automatically:

DB::transaction(function () {
    $sender = DB::table('users')
        ->lockForUpdate()
        ->find(1);

    $receiver = DB::table('users')
        ->lockForUpdate()
        ->find(2);

    if ($sender->balance < 100) {
        throw new RuntimeException('Balance too low.');
    }

    DB::table('users')
        ->where('id', $sender->id)
        ->update([
            'balance' => $sender->balance - 100
        ]);

    DB::table('users')
        ->where('id', $receiver->id)
        ->update([
            'balance' => $receiver->balance + 100
        ]);
});

Reusable Query Components

If you have repeated query logic throughout your application, you may extract the logic into reusable objects using the query builder's tap and pipe methods. Imagine you have these two different queries in your application:

use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;

$destination = $request->query('destination');

DB::table('flights')
    ->when($destination, function (Builder $query, string $destination) {
        $query->where('destination', $destination);
    })
    ->orderByDesc('price')
    ->get();

// ...

$destination = $request->query('destination');

DB::table('flights')
    ->when($destination, function (Builder $query, string $destination) {
        $query->where('destination', $destination);
    })
    ->where('user', $request->user()->id)
    ->orderBy('destination')
    ->get();

You may like to extract the destination filtering that is common between the queries into a reusable object:

<?php

namespace App\Scopes;

use Illuminate\Database\Query\Builder;

class DestinationFilter
{
    public function __construct(
        private ?string $destination,
    ) {
        //
    }

    public function __invoke(Builder $query): void
    {
        $query->when($this->destination, function (Builder $query) {
            $query->where('destination', $this->destination);
        });
    }
}

Then, you can use the query builder's tap method to apply the object's logic to the query:

use App\Scopes\DestinationFilter;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;

DB::table('flights')
    ->when($destination, function (Builder $query, string $destination) { // [tl! remove]
        $query->where('destination', $destination); // [tl! remove]
    }) // [tl! remove]
    ->tap(new DestinationFilter($destination)) // [tl! add]
    ->orderByDesc('price')
    ->get();

// ...

DB::table('flights')
    ->when($destination, function (Builder $query, string $destination) { // [tl! remove]
        $query->where('destination', $destination); // [tl! remove]
    }) // [tl! remove]
    ->tap(new DestinationFilter($destination)) // [tl! add]
    ->where('user', $request->user()->id)
    ->orderBy('destination')
    ->get();

Query Pipes

The tap method will always return the query builder. If you would like to extract an object that executes the query and returns another value, you may use the pipe method instead.

Consider the following query object that contains shared pagination logic used throughout an application. Unlike the DestinationFilter, which applies query conditions to the query, the Paginate object executes the query and returns a paginator instance:

<?php

namespace App\Scopes;

use Illuminate\Contracts\Pagination\LengthAwarePaginator;
use Illuminate\Database\Query\Builder;

class Paginate
{
    public function __construct(
        private string $sortBy = 'timestamp',
        private string $sortDirection = 'desc',
        private string $perPage = 25,
    ) {
        //
    }

    public function __invoke(Builder $query): LengthAwarePaginator
    {
        return $query->orderBy($this->sortBy, $this->sortDirection)
            ->paginate($this->perPage, pageName: 'p');
    }
}

Using the query builder's pipe method, we can leverage this object to apply our shared pagination logic:

$flights = DB::table('flights')
    ->tap(new DestinationFilter($destination))
    ->pipe(new Paginate);

Debugging

You may use the dd and dump methods while building a query to dump the current query bindings and SQL. The dd method will display the debug information and then stop executing the request. The dump method will display the debug information but allow the request to continue executing:

DB::table('users')->where('votes', '>', 100)->dd();

DB::table('users')->where('votes', '>', 100)->dump();

The dumpRawSql and ddRawSql methods may be invoked on a query to dump the query's SQL with all parameter bindings properly substituted:

DB::table('users')->where('votes', '>', 100)->dumpRawSql();

DB::table('users')->where('votes', '>', 100)->ddRawSql();

查询构造器 12.x 12.x 11.x 10.x 9.x 8.5 8.x 7.x 6.x 5.8 5.7 5.6 5.5 5.4 5.3 5.2 5.1

Database: Query Builder

Introduction

运行数据库查询

从表中检索所有记录

从表中检索单行或单列

获取某一列的值列表

分块结果

延迟流式结果

Aggregates

Determining if Records Exist

Select Statements

Specifying a Select Clause

Raw Expressions

Raw Methods

selectRaw

whereRaw / orWhereRaw

havingRaw / orHavingRaw

orderByRaw

groupByRaw

Joins

Inner Join Clause

Left Join / Right Join 子句

交叉连接子句

高级连接子句

Subquery Joins

Lateral Joins

Unions

Basic Where Clauses

Where Clauses

Or Where 子句

Where Not 子句

Where Any / All / None 子句

JSON Where Clauses

附加 Where 子句

逻辑分组

高级 Where 子句

Where Exists 子句

子查询 Where 子句

全文 Where 子句

排序、分组、条数限制和偏移量

排序

orderBy 方法

latest 和 oldest 方法

随机排序

移除现有排序

分组

groupBy 和 having 方法

条数限制和偏移量

The skip 和 take 方法

前提子句

插入语句

自增 ID

更新插入

更新语句

Update or Insert

Updating JSON Columns

Increment and Decrement

Delete Statements

Pessimistic Locking

Reusable Query Components

Query Pipes

Debugging

推荐文章：

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

请登录

查询构造器

12.x

12.x 11.x 10.x 9.x 8.5 8.x 7.x 6.x 5.8 5.7 5.6 5.5 5.4 5.3 5.2 5.1

`selectRaw`

`whereRaw / orWhereRaw`

`havingRaw / orHavingRaw`

`orderByRaw`

`groupByRaw`

`orderBy` 方法

`groupBy` 和 `having` 方法

The `skip` 和 `take` 方法