Binding Parameters in Raw SQL Within Eloquent Queries
When leveraging Laravel's Query Builder for complex geospatial calculations, you often find yourself injecting DB::raw() clauses to handle custom SQL logic like the Haversine formula. The standard approach involves passing an array of values directly into the select or where clause alongside the raw string. While this works seamlessly with simple conditions, it breaks down when mixing named placeholders (like :lat) with positional bindings in a single query block.
Consider a scenario where you are querying properties based on proximity to a specific coordinate. You might draft a query that looks like this:
$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")
)
->having("distance", "<", $radius)
->orderBy("distance")
->take(20)
->get();
If you attempt to pass the binding array directly to select(), such as ->select(DB::raw(...), ["lat" => $lat, "lng" => $lng]), Laravel throws an exception: Invalid parameter number: mixed named and positional parameters. This error occurs because the raw SQL string contains named placeholders (:lat), which expect a specific binding type. However, if you accidentally mix that with a separate array of positional bindings intended for a different part of the query, or if the syntax implies an array argument where a single raw expression is expected, Laravel's internal parameter binding logic gets confused. It cannot reconcile named placeholders against a context that might be interpreted as supporting positional arguments.
The root cause is how Eloquent constructs the SQL string before sending it to the database. When you pass an array of bindings to select(), Laravel expects all subsequent raw expressions in that specific call to utilize positional markers (?) rather than named ones (:name). Conversely, if your raw string uses named placeholders, you must ensure the binding context explicitly supports them without ambiguity.
To resolve this while retaining the readability of the fluent interface, you need to separate the binding logic from the raw expression definition. The most reliable method is to pass the bindings as an associative array directly to the select() call when using named parameters, ensuring no positional arguments are implied in that specific scope.
Here is the corrected implementation:
$bindings = [
'lat' => $lat,
'lng' => $lng,
];
$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")
)
->where('distance', '<', $radius)
->orderBy('distance')
->take(20)
->get(['*']); // Ensure you cast the raw result back to a model or access properties carefully
Wait, there is a nuance here regarding the placeholder syntax. If you are using named parameters like :lat in your raw string, you must use the named bindings correctly without mixing positional ones. The error Invalid parameter number: mixed named and positional parameters specifically implies that somewhere in the query construction, Laravel detected an array intended for positional binding (?) while the SQL contained named placeholders.
A safer pattern involves using the selectRaw method with explicit bindings passed as a second argument, ensuring the driver knows exactly how to map the variables. However, if you are strictly within the select() chain and want to keep the structure clean, ensure that the binding array is passed explicitly and that the raw string uses consistent placeholders.
If your environment requires strict adherence to named parameters, verify that you aren't inadvertently passing a mixed array. The most robust fix is often to use DB::raw with explicit bindings or switch to double curly braces {{}} if you are using Laravel 9+, which automatically handles the binding context more gracefully when paired with the correct binding array syntax.
Another approach is to define the raw expression separately and bind it via a closure or by utilizing the query builder's ability to accept an array of bindings at the very end of the chain, but only if the raw string inside uses ? placeholders. Since you are using :lat, you must ensure the binding array is treated as named bindings exclusively.
$bindings = ['lat' => $lat, 'lng' => $lng];
$property = Property::selectRaw(
"title, lat, lng, (
3959 * acos(
cos( radians({{lat}}) ) *
cos( radians( lat ) ) *
cos( radians( lng) - radians({{lng}}) ) +
sin( radians({{lat}}) ) *
sin( radians( lat ) )
) AS distance"
),
$bindings
)->where('distance', '<', $radius)
->orderBy('distance')
->take(20)
->get();
Using selectRaw explicitly makes the intent clear and allows passing the bindings array immediately after, ensuring Laravel treats them as named parameters corresponding to the placeholders in the string. This avoids the ambiguity that triggers the "mixed parameters" exception. Always verify your placeholder syntax matches the binding style required by your specific database driver and Laravel version to prevent runtime errors involving parameter mapping.
