How to bind parameters to a raw DB query in Laravel that's used on a model?

Question

Re,

I have the following query:

$property = 
    Property::select(
        DB::raw("title, lat, lng, ( 
            3959 * acos( 
                cos( radians(:lat) ) * 
                cos( radians( lat ) ) * 
                cos( radians( lng ) - radians(:lng) ) + 
                sin( radians(:lat) ) * 
                sin( radians( lat ) ) 
            ) 
        ) AS distance", ["lat" => $lat, "lng" => $lng, "lat" => $lat])
    )
    ->having("distance", "<", $radius)
    ->orderBy("distance")
    ->take(20)
    ->get();

It doesn't work: Invalid parameter number: mixed named and positional parameters.

Does anyone know a trick or a workaround (I can obviously write the full query but prefer to use fluent builder).

Silly question - but the documentation doesn't use `" "` in any of its examples. Have you tried singles? — James Binford, Jan 01 '14 at 02:37
@JamesBinford yup, tried all possible combinations, including ```%s``` — MarkL, Jan 01 '14 at 08:41
Models don't really exist. You meant "used on an instance". A model is just a blank template. In the Laravel world everyone's using the wrong terminology, "model" when they mean "instance". — GeneC, Jul 23 '21 at 04:02

score 116 · Accepted Answer · edited Apr 23 '16 at 17:55

116

OK, after some experimenting, here's the solution that I came up with:

$property = 
    Property::select(
        DB::raw("title, lat, lng, ( 
            3959 * acos( 
                cos( radians(  ?  ) ) *
                cos( radians( lat ) ) * 
                cos( radians( lng ) - radians(?) ) + 
                sin( radians(  ?  ) ) *
                sin( radians( lat ) ) 
            )
       ) AS distance")
    )
    ->having("distance", "<", "?")
    ->orderBy("distance")
    ->take(20)
    ->setBindings([$lat, $lng, $lat,  $radius])
    ->get();

Basically, setBindings has to be called on the query. Wish this was documented!

edited Apr 23 '16 at 17:55

DutGRIFF

5,103
1
33
42

answered Jan 01 '14 at 20:38

MarkL

8,770
7
26
27

3

+1 Was looking for this exact answer...including the map radius query. Thanks! – Joe Jun 19 '14 at 08:59
3

setBindings doesn't appear to support PDO's named parameters :/ – carbontwelve Aug 12 '14 at 11:36
2

If you are working over a relation, for instance, you should use `addBinding` instead of `setBindings` to avoid overwrite relation bindings. – David Rodrigues Oct 02 '15 at 21:21
7

This is the signature of `setBindings(array $bindings, $type = 'where')`. If you would have had a binding in your WHERE clause, those would've been overwritten. You should specify that your bindings are for the SELECT portion of your query. `->setBindings([$lat, $lng, $lat, $radius], 'select')` – Swimburger May 31 '16 at 23:32
Worked for me also, although it didnt play nicely with ->where(), so I had to bind that too – Edmunds22 Jul 14 '16 at 15:41
Note that the bindings are positional only. You can still use named bindings for clarity in the code, such as `:radians`, but they will be treated as unnamed, i.e. `?`. – Jason Apr 16 '20 at 12:53

score 43 · Answer 2 · edited Oct 10 '17 at 07:44

43

Old question, but if we have to repeat a variable, we have to change its key value in the bindings array.

    $property = Property::select(
        DB::raw("title, lat, lng, ( 3959 * acos( cos( radians(:lat) ) * 
        cos( radians( lat ) ) * cos( radians( lng ) - radians(:lng) ) + 
        sin(radians(:lat_i) ) * sin( radians( lat ) ) ) ) AS distance"),
        ["lat" => $lat, "lng" => $lng, "lat_i" => $lat]);

That's enough.

edited Oct 10 '17 at 07:44

Valdrinium

1,398
1
13
28

answered Feb 10 '15 at 11:47

bluesky777

400
6
23

1

Laaame. Thank you, sir. – Chords Jul 15 '15 at 01:23
1

In which version can you put bindings as the second parameter of DB::raw? – phazei Jul 28 '15 at 02:37
I use it in Laravel 4.2 – bluesky777 Jul 28 '15 at 22:11
9

In 4.2 you can use bindings as the second parameter in whereRaw(), but not in DB::raw(). In the example above, the bindings are getting passed to select(), not DB::raw(). – Karl Aug 14 '15 at 05:03
2

I'm getting an error "strtolower() expects parameter 1 to be string, array given" – kiradotee May 22 '18 at 19:40
Ok, apparently (not sure if intended?) but using `$modelClass::select(DB::raw($queryString), $arrayOfParameters)` gives that error. BUT if I use `$modelClass::selectRaw($queryString, $arrayOfParameters)` it's all hunky-dory! – kiradotee May 22 '18 at 19:57

score 12 · Answer 3 · answered Feb 25 '16 at 15:06

why not?

    $latitude = $request->input('latitude', '44.4562319000');
    $longitude = $request->input('longitude', '26.1003480000');
    $radius = 1000000;

    $locations = Locations::selectRaw("id, name, address, latitude, longitude, image_path, rating, city_id, created_at, active,
                         ( 6371 * acos( cos( radians(?) ) *
                           cos( radians( latitude ) )
                           * cos( radians( longitude ) - radians(?)
                           ) + sin( radians(?) ) *
                           sin( radians( latitude ) ) )
                         ) AS distance", [$latitude, $longitude, $latitude])
        ->where('active', '1')
        ->having("distance", "<", $radius)
        ->orderBy("distance")
        ->get();

This is a 2-year question. `selectRaw` was not available in back then. — MarkL, Feb 25 '16 at 19:34

score 4 · Answer 4 · answered Aug 15 '16 at 01:59

I encountered same issue just recently and the answer is in that error message mixed named and positional parameters. In your case, the :lat and :lng are named parameters while you have $radius as positional. So one possible fix to your issue is make use of havingRaw() and apply named parameters.

--havingRaw('distance < :radius', ['radius' => $radius])

score 1 · Answer 5 · answered Dec 30 '15 at 14:38

$select = <<<SQL
    title,
    lat,
    lng,
    (3959*acos(cos(radians( ? ))*cos(radians(lat))*cos(radians(lng)-radians( ? ))+sin(radians( ? ))*sin(radians(lat)))) AS distance
SQL;

$property = Property::selectRaw($select, [$lat, $lng, $lat])
    ->having('distance', '<', $radius)
    ->orderBy('distance')
    ->take(20)->get();

score 0 · Answer 6 · answered Oct 01 '16 at 21:54

I ported the nearby search from Doctrine v1 to Laravel, check it out here.

Just add the Geographical trait to the model then you can do:

$model->newDistanceQuery($request->query('lat'), $request->query('lon'))->orderBy('miles', 'asc')->get();

It works by using selectRaw with bindings like this:

$sql = "((ACOS(SIN(? * PI() / 180) * SIN(" . $latName . " * PI() / 180) + COS(? * PI() / 180) * COS(" . $latName . " * PI() / 180) * COS((? - " . $lonName . ") * PI() / 180)) * 180 / PI()) * 60 * ?) as " . $unit;
if($kilometers){
    $query->selectRaw($sql, [$lat, $lat, $lon, 1.1515 * 1.609344]);
}
else{
    // miles
    $query->selectRaw($sql, [$lat, $lat, $lon, 1.1515]);
}

How to bind parameters to a raw DB query in Laravel that's used on a model?

6 Answers6

Linked