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).

Answer 1

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!

Answer 2

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.