Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
750 views
in Technique[技术] by (71.8m points)

php - Laravel query builder parameter binding

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

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

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

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

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

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...