[SOLVED] Make CakePHP querybuilder wrap a subquery in parentheses

Make CakePHP querybuilder wrap a subquery in parentheses

How do I wrap a subquery in extra parentheses?

$sub = new \Cake\Database\Query();
$sub
  ->select('b.value')
  ->from(['b' => 'other_table'])
  ->where(['b.key' => 'fancy_cars'])
;

$query = new \Cake\Database\Query();
$query
  ->select(['a.id', 'a.name'])
  ->from(['a' => 'car'])
  ->where(['a.id MEMBER OF' => $sub])
;

Background: I have a table with a JSON value in one of its cells, which is a list of IDs.

-- single parentheses is syntax error.
SELECT a.id, a.name FROM car AS a
WHERE a.id MEMBER OF (SELECT b.value FROM other_table AS b WHERE b.key = 'fancy_cars');

-- but this works.
SELECT a.id, a.name FROM car AS a
WHERE a.id MEMBER OF ((SELECT b.value FROM other_table AS b WHERE b.key = 'fancy_cars'));

-- underlying JSON value for comprehension.
SELECT id, name FROM my_resource WHERE id MEMBER OF ('[4, 192, 29, …]');

How do I force another layer of parentheses? Things I've tried:

#1: another subquery nesting layer?
// … build sub the same way.
$sub2 = new \Cake\Database\Query();
$sub2->select($sub);

// …
$query->where(['a.id MEMBER OF' => $sub2]);

// ---

#2: array brackets?
$query->where(['a.id MEMBER OF' => [$sub]]);

cakephp/database 3.8.1

Solution

newExpr can do it.

$query->where(['a.id MEMBER OF' => $query->newExpr($sub)]);