How to Properly Bind Parameters in Complex Raw Queries in Laravel?

Querying with Bindings: A Practical Solution in Laravel

When working with complex raw database queries in Laravel, binding parameters is crucial to prevent errors. This situation often arises when using a model-based approach.

Consider the following query encountered by a user:

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

This query attempts to filter properties based on their distance from a given point using a raw SQL calculation. However, an error occurs because of mixed named and positional parameters.

To resolve this issue, one should leverage the setBindings method, which allows for explicitly specifying the query's bindings. Here's the modified and working code:

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

By using positional placeholders (?) in the raw SQL and invoking setBindings with the appropriate array of values, the query is executed properly, binding the parameters to the appropriate placeholders.

The above is the detailed content of How to Properly Bind Parameters in Complex Raw Queries in Laravel?. For more information, please follow other related articles on the PHP Chinese website!