How to Correctly Bind Parameters to Raw Queries in Laravel\'s Eloquent Builder?

Binding Parameters to Raw Queries in Laravel's Eloquent Builder

When using a raw database query within Laravel's Eloquent model builder, it can be challenging to bind parameters correctly. One common issue arises when attempting to utilize both positional and named parameters within the same query.

The query provided in the question illustrates this issue:

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

Upon execution, this query fails with the error "Invalid parameter number: mixed named and positional parameters."

To resolve this issue, one must bind the parameters explicitly using the setBindings() method. This method accepts an array of the parameters in the order they appear in the query.

Here is the modified query with the setBindings() method:

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 the setBindings() method correctly, the query can now be executed successfully, binding the parameters to the appropriate placeholders in the raw SQL.

The above is the detailed content of How to Correctly Bind Parameters to Raw Queries in Laravel\'s Eloquent Builder?. For more information, please follow other related articles on the PHP Chinese website!