Paninozzo's avatar

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?

0 likes
0 replies

Please or to participate in this conversation.