Binding issue with ?| operator using whereRaw with Postgres

[raphaelmsr]

• Laravel Version: 6.6.0
• PHP Version: 7.4.1
• Database Driver & Version: PostgreSQL 10.11

Description:

I've been trying to query whereRaw with a ?| operator but although it is in the PostgresGrammar, it keeps on being binded by a value, either being replaced if I give a variable, or a binding error regarding the counts of binded parameters
Currently i am using jsonb_exists_any but it is somehow much slower to ?|

Steps To Reproduce:

Query :
$model->whereRaw('("column")::jsonb ?| array?', str_replace('"',"'",json_encode($array)));

with pgsql driver will replace ?| with the first binded paramater of whereRaw

EDIT :
I know why it is much slower now ...
https://dba.stackexchange.com/questions/90002/postgresql-operator-uses-index-but-underlying-function-does-not

[staudenmeir]

PHP 7.4 allows you to escape the placeholder: https://wiki.php.net/rfc/pdo_escape_placeholders

You also need to pass the array differently:

$binding = json_encode($array);

$model->whereRaw('("column")::jsonb ??| array(select json_array_elements_text(?))', $binding);

[raphaelmsr]

You saved my life so many times .. thanks a million, it seems to work !!
But why wouldnt the PostgresGrammar class do it its job of not binding ?| ?

PS : I have a custom function to get the SQL query along with the binded parameters that uses :
$sql = preg_replace('/\?/', $value, $sql, 1);
Do you happen to know of a way to only replace ? and not ??| and only the ? in ?:: ?

[staudenmeir]

But why wouldnt the PostgresGrammar class do it its job of not binding ?| ?

• It looks like nobody has tested/used these operators.
• The grammar can't prevent the binding of ?|, that is/was a PHP issue.
• The grammar can't/shouldn't escape ? in raw expressions. It could do that for queries like where('column', '?', $key) or at least support where('column', '??', $key).
• There is no workaround for PHP < 7.4.

[raphaelmsr]

EDIT :
Nevermind, Its all good it works perfectly,
That's crazy to me.. Luckily i'm under PHP 7.4