3

I'm having an issue when using bind parameters in my DQL statement.

I've created a JSONB DBAL data type and a FunctionNode called JSON_CONTAINS_ANY() function. The final query that I want is:

SELECT * from Cache where content->'status' ?| ['started','inprogress'];

My DQL statement looks as follow:

$dql = "SELECT e FROM Entity e WHERE JSON_CONTAINS_ANY(content, 'status', :statusList";
$dql->setParameter('statusList',$statusList, Jsonb::JSONB);

Question: The problem is the the ?| operator gets treated as a parameter expansion index and screws up my queries. Any ideas on how to handle this?

Reza S
  • 9,480
  • 3
  • 54
  • 84
  • Shortly after asking this question I found the answer on: http://stackoverflow.com/questions/18404055/index-for-finding-an-element-in-a-json-array . Not sure what the etiquette is on this, should I leave the question open with the link or delete it? – Reza S Jul 24 '15 at 19:05

1 Answers1

0

I don't know if my answer will be relevant, I see the question was asked 6 years ago, but, more elegant solution appeared literally 2 years ago, then can someone help. You can read in detail at this link https://wiki.php.net/rfc/pdo_escape_placeholders from the description I will take out that now ? you can get screened in this way ?? as described in:

That means that the “??” string would be translated to “?” when sending the query to the database, whereas “?” is still going to be interpreted as a positional parameter placeholder.

$stmt = $pdo->prepare('SELECT * FROM tbl WHERE json_col ?? ?');
$stmt->execute(['foo']);
UKRman
  • 404
  • 3
  • 16