whereRaw() bindings behaviour unclear
Hello everyone, I'm testing a solution to build a query looking for a JSON value in any json property present in the column. SQL-wise would be something like this
SELECT * FROM brands WHERE JSON_CONTAINS(JSON_EXTRACT(slug, "$.*"), '"some-slug"', '$')
This works fine in SQL, the logic is solid (maybe not the best performance wise, but that's another topic). Fact is when I traslate this to eloquent I get different behaviours depending how I write the query
I'm using a scope, so it's a Illuminate\Database\Eloquent\Builder i'm returning
Example 1
return $query->whereRaw("JSON_CONTAINS( JSON_EXTRACT(slug, \"$.*\"), '\"some-slug\"', '$' )")
Works as intended, everything is hard coded but performs query.
Example 2
return $query->whereRaw("JSON_CONTAINS( JSON_EXTRACT($field, \"$.*\"), '\"?\"', '$' )", [$value])
With the value binded purely and surrounded with quotes as per function necessity, compiles the query correctly but throws Invalid parameter number
SQLSTATE[HY093]: Invalid parameter number (SQL: select * from `brands` where JSON_CONTAINS( JSON_EXTRACT(slug, '$.*'), '"some-slug"', '$' )
Example 3
return $query->whereRaw("JSON_CONTAINS( JSON_EXTRACT($field, \"$.*\"), ?, '$' )", ['\'"' . $value . '"\''])
Still the query gets compiled correctly but i get Invalid JSON text in argument 1 to function json_contains
SQLSTATE[22032]: <<Unknown error>>: 3141 Invalid JSON text in argument 1 to function json_contains: "Invalid value." at position 0. (SQL: select * from `brands` where JSON_CONTAINS( JSON_EXTRACT(slug, "$.*"), '"some-slug"', '$' ) limit 1).
In Example 2 and 3 even if I hard code slug to $filed in the string literal, nothing changes
Now the question is: provided that the query syntax is correct, why can't I bind a value? Is this a bug? Am I doing something wrong? Is this behaviour actually expected?
Please or to participate in this conversation.