Yii2 DAC + PostgreSQL JSONB

204
03 марта 2019, 19:20

Исходные данные:

SQL выборка по колонке типа JSONB

SELECT * FROM project where guest_users ?| array['abcd'];

Обращаться к БД пробовал тремя способами:

1) через DAC

$user_name = 'abcd';
$db = Yii::$app->db;
$res = $db->createCommand("select * 
   from project 
   where guest_users ?| array['".$user_name."']")
  ->queryOne();

2) через Query

   $user_name = 'abcd';
   $res = (new Query())->select(*)
     ->from(Project::tableName())
     ->where([
        "?|",
        "guest_users", 
        new Expression("array['".$user_name."']")
      ])
      ->one();

3) через Query c Expression

$user_name = 'abcd';
$res = (new Query())->select(*)
   ->from(Project::tableName())
   ->where([
      new Expression("?|"),
      "guest_users",
      new Expression("array['".$user_name."']")
   ])
   ->one();

Вне зависимости от формы обращения штатный yii\db\Exception не пропускает запрос на исполнение т.к. знак вопроса подменяется знаком доллара (? на $), хотя возвращает рабочий SQL

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$1" LINE 1: ...ct" FROM "project" WHERE "guest_users" $1| array[... ^ The SQL being executed was: SELECT * FROM "project" WHERE "guest_users" ?| array['abcd']

Answer 1

Проблема уходит в API prepared statements в PDO, который парсит строку запроса в поиске параметров неименованных ? и успешно такой находит там где не надо. Есть и родственный баг в трекере.

То есть корень проблемы на два уровня глубже и иллюстрируется вот так:

$pdo = new pdo('pgsql:', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
$s = $pdo->prepare("select '{}'::jsonb ?| array['foo']");
$s->execute();

Баг именно в PHP, прямыми способами не чинится. Но postgresql штука весьма гибкая и могу предложить пару способов обхода проблемы со стороны базы:

Узнать, что любой оператор - это функция

Оператору ?| между JSONB и text[] соответствует функция jsonb_exists_any. Её можно вызывать напрямую:

where jsonb_exists_any(guest_users, array[...])

Сделать хранимку и вызывать вместо оператора её

CREATE FUNCTION jsonb_keys_exists(JSONB,TEXT[]) 
RETURNS BOOLEAN LANGUAGE SQL IMMUTABLE AS $f$
  SELECT $1 ?| $2
$f$;

Тогда получится

where jsonb_keys_exists(guest_users, array[...])

Определить свои собственные операторы

CREATE OPERATOR ~@| (LEFTARG = jsonb, RIGHTARG = text[], PROCEDURE = jsonb_exists_any);

И использовать этот оператор:

where guest_users ~@| array[...]
READ ALSO
Как сделать сложный SQL запрос в yii2
PHP

Как сделать сложный SQL запрос в yii2

Пытаюсь выполнить такой запрос в yii2:

 256
migrate через файл php (YII)
PHP

migrate через файл php (YII)

есть задача реализовать миграции через файл install подскажите можно ли запустить миграции не через консоль а через файл php ?

 205
Как сравнить и записать в файл одинаковые строки?
PHP

Как сравнить и записать в файл одинаковые строки?

Есть 3 текстовых файла

 437
PHP Запись массива в строку рекурсией
PHP

PHP Запись массива в строку рекурсией

есть такой массив и функция, при обычном выводе (echo) всё корректно выводится, но при записывании в строку массив записывается в не понятном...

 184