In Laravel, how do you bind parameters to a query in query builder DB raw inside another query?

Question

I have two queries that I'm building:

$color = \DB::table('colors')->take(10)->orderBy('id', 'desc');

if(isset($between)){
    $color->whereBetween('id', [$start, $end]);
}

$flavor = \DB::table(\DB::raw('(' . $color->toSql() . ') as color'))
               ->join('flavor', 'flavor.color_id', '=', 'color.id')
               ->select('color.id', 'color.name', 'flavor.name');

return $flavor->get();

I need to bind 2 values in the first query only IF there is a $between set. And it has to be inside that \DB::raw.

How do I do that?

P.S if I run the first query separately it works perfectly. But when I try to run the whole query at once I'm getting General error 2031 (basically the parameters are not bind).

lagbox · Accepted Answer · 2019-11-17 03:00:03Z

2

You could try merging the bindings from the first query into the second query before executing it:

$flavor->mergeBindings($color);

Or just the binding without the 'type' of the bindings:

$flavor->addBinding($color->getBindings());

The second one should work because you only have a binding for a where which is the default type for addBinding. For more advanced queries with other bindings you probably want to go the way of merging the bindings so the 'type' of the binding is accurate.

edited Nov 17, 2019 at 3:00

answered Nov 17, 2019 at 1:49

lagbox

50.7k8 gold badges76 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Melody Over a year ago

I'm getting this error:

Argument 1 passed to Illuminate\Database\Query\Builder::mergeBindings() must be an instance of Illuminate\Database\Query\Builder, array given

Melody Over a year ago

i've used this instead and it works(update the answer with this in it so i can choose it:D): $flavor->addBinding($color->getBindings());

TsaiKoga · Accepted Answer · 2019-11-17 03:04:38Z

2

Firstly, change your DB::table('color') to your Model name like

Color::take(10)->orderBy('id', 'desc')

Secondly, add

->mergeBindings($color->getQuery)

Something like this:

use App\Models\Color;
...
$color = Color::take(10)->orderBy('id', 'desc');

if(isset($between)){
    $color->whereBetween('id', [$start, $end]);
}

$flavor = \DB::table(\DB::raw('(' . $color->toSql() . ') as color'))
               ->mergeBindings($color->getQuery())
               ->join('flavor', 'flavor.color_id', '=', 'color.id')
               ->select('color.id', 'color.name', 'flavor.name');


return $flavor->get();

edited Nov 17, 2019 at 3:04

answered Nov 17, 2019 at 2:39

TsaiKoga

13.4k2 gold badges23 silver badges31 bronze badges

Collectives™ on Stack Overflow

In Laravel, how do you bind parameters to a query in query builder DB raw inside another query?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related