How to Bind Parameters to Raw Database Queries in Laravel for Eloquent Models?

Binding Parameters to Raw Database Queries in Laravel for Eloquent Models

Utilizing raw database queries with Laravel's Eloquent models provides flexibility and allows for advanced operations. However, binding parameters to such queries can prove challenging.

In the provided query:

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

It's essential to note that the query uses named placeholders (? with names matching the bound parameter keys) within the DB::raw.

The key to binding parameters in this scenario lies in utilizing the setBindings method. This method takes an array of values to be bound to the query parameters. In the example:

• $lat and $lng are bound to the ? placeholders representing latitudes and longitudes.
• $lat is bound again to account for the second positional argument in the cosine function.
• $radius is bound to the ? placeholder in the having clause.

By calling setBindings at the end of the query chain, the parameter values are properly assigned. This solves the "Invalid parameter number: mixed named and positional parameters" error and allows for proper execution of the raw database query.

It's worth noting that this solution is not documented explicitly, which can be frustrating. However, understanding the underlying mechanics and employing the provided workaround enables you to utilize raw database queries effectively within Laravel's Eloquent models.

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