Laravel查询构建器参数绑定 [英] Laravel query builder parameter binding

问题描述

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

I'm trying to bind the same value to some parameter in a raw query (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();

有没有办法一次绑定相同的参数(防止在[2,2,2]中重复值)?

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

推荐答案

使用命名参数. 数据库"页面的运行原始SQL查询"部分中的文档中对此进行了介绍，在使用命名绑定"子标题下.报价:

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:

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

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

您应该可以运行此程序:

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

但是Laravel似乎在消息Invalid parameter number中抛出了QueryException.我将其报告为一个错误.

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

如果您真的想使用whereRaw，则可以改用变量来构建参数数组:

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

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

或使用 array_fill 为您重复该值:

Or use array_fill to repeat the value for you:

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

如果不需要whereRaw，则可以使用查询生成器的其他功能并通过参数来自变量的方式逐步构建查询:

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

$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 docs for some examples.

这篇关于Laravel查询构建器参数绑定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！