Laravel 查询构建器参数绑定

Laravel query builder parameter binding

提问by amin

I'm trying to bind the same value to some parameter in a raw query (Laravel 5.2)

我正在尝试将相同的值绑定到原始查询中的某个参数（Laravel 5.2）

//this is a non practical example ,only for clarify the question

DB::table('users as u')
->select('id')
->whereRaw('u.id > ? or u.id < ? or u.id = ?',[2,2,2])
->first();

is there any way to bind the same parameters at once(prevent duplicating values in [2,2,2])?

有没有办法一次绑定相同的参数（防止 [2,2,2] 中的值重复）？

回答by tremby

Use named parameters. They're covered in the documentation in the Running Raw SQL Queries section of the Database page, under the subheading Using Named Bindings. Quoting:

使用命名参数。它们包含在数据库页面的运行原始 SQL 查询部分的文档中，在副标题使用命名绑定下。引用：

Instead of using ?to represent your parameter bindings, you may execute a query using named bindings:

$results = DB::select('select * from users where id = :id', ['id' => 1]);

?您可以使用命名绑定执行查询，而不是用于表示您的参数绑定：

$results = DB::select('select * from users where id = :id', ['id' => 1]);

In your case you ought to be able to run this:

在你的情况下，你应该能够运行这个：

DB::table('users as u')
    ->select('id')
    ->whereRaw('u.id > :id or u.id < :id or u.id = :id', [
        'id' => 2,
    ])
    ->first();

But it seems Laravel throws a QueryExceptionwith the message Invalid parameter number. I've reported this as a bug.

但似乎 Laravel 抛出了一个QueryException带有消息的Invalid parameter number。我已将此报告为错误。

If you really want to use whereRawyou could instead build your array of parameters from a variable:

如果你真的想使用whereRaw你可以从一个变量构建你的参数数组：

$id = 2;
DB::table('users as u')
    ->select('id')
    ->whereRaw('u.id > ? or u.id < ? or u.id = ?', [
        $id, $id, $id,
    ])
    ->first();

Or use array_fillto repeat the value for you:

或用于array_fill为您重复该值：

$id = 2;
DB::table('users as u')
    ->select('id')
    ->whereRaw('u.id > ? or u.id < ? or u.id = ?', array_fill(0, 3, $id))
    ->first();

If you don't need whereRawyou can instead use other features of the query builder and build the query bit by bit, with the parameter coming from a variable:

如果您不需要，whereRaw您可以改用查询构建器的其他功能并一点一点地构建查询，参数来自一个变量：

$id = 2;
DB::table('users')
    ->select('id')
    ->where('id', '>', $id)
    ->orWhere('id', '<', $id)
    ->orWhere('id', $id')
    ->first();

The query builder is quite powerful, and to get more complicated logic you can nest closures. See the relevant section of the docsfor some examples.

查询构建器非常强大，为了获得更复杂的逻辑，您可以嵌套闭包。有关一些示例，请参阅文档的相关部分。