Laravel whereRaw - how to escape a '?'

user_12232392 0
Viewed 941

Is there a way to escape a "?" in a eloquent whereRaw Statement? (Using laravel 6.x)

example:

ExampleModel::whereRaw(' "table"."json_field"::jsonb ?| array[\'test\', \'test2\'] ')->get();

This gets sent to the db as

where "table"."json_field"::jsonb $1| array['test', 'test2']

And well, thats not what i wanted to query...

Tried with '\?', put it in a binding (Laravel doc) - still no success. Also i didn't find a reference in the docs ...

In my Usecase i want it to compare a json object with the psql comparing "?|" (Postgres Doc)

Thanks in advance!

eloquent laravel postgresql escaping
3 Answers

the question is pretty old but my answer might help anyway.

The solution is very simple: just escape the questionmark with another questionmark.

The code of your example would look like this:

ExampleModel::whereRaw(' "table"."json_field"::jsonb ??| array[\'test\', \'test2\'] ')->get();

This solution has been tested with Laravel 7.28.3.

user_147118140

Didn't test but try using PDO:

$whereRaw = DB::connection()->getPdo()->quote(' "table"."json_field"::jsonb ?| array["test", "test2"] ');

ExampleModel::whereRaw($whereRaw)->get();
user_69357630

Well, didn't find an answer, but a workaround:

Don't use the question mark operators!

Instead i went for the named function. I found the named function via 

SELECT 
  oprname, 
  oprcode || '(' || format_type(oprleft,  NULL::integer) || ', ' 
                 || format_type(oprright, NULL::integer) || ')' AS function
FROM pg_operator 
WHERE oprname = '?|';

( Found there: big thanks to this guys post!)

So my Eloquent query now looks like:

ExampleModel::whereRaw('jsonb_exists_any("table"."json_field"::jsonb, array[\'test\', \'test2\'])')->get();

At least its working ¯\_(ツ)_/¯

user_122323920
Related