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

05/23/2020 23:00:02

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

Verified Answer (96 Votes)

01/02/2014 01:38:24

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!

96

Answer #2 (31 Votes)

02/10/2015 16:47:36

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.

31

Answer #3 (11 Votes)

02/25/2016 20:06:57

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();
11
3
Hack Hex uses Stack Exchance API by the Stack Exchange Inc. to scrape questions/answers under Creative Commons license.