Doctrine + Postgres — как составить QueryBuilder с поиском по JSON?

Question

[HellWalk]

Есть рабочий SQL запрос:

SELECT * FROM "user" WHERE "roles"::text = '["ROLE_COURIER"]';

Нужно составить его на QueryBuilder.

Если написать так:

$role = '["ROLE_COURIER"]';

        return $this->createQueryBuilder('user')
            ->where('user.roles = :role')
            ->setParameter('role', $role)
            ->getQuery()
            ->getResult()
            ;

Выдает ошибку:

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json = unknown

Если написать так:

$role = '["ROLE_COURIER"]';

        return $this->createQueryBuilder('user')
            ->where('"roles"::text = :role')
            ->setParameter('role', $role)
            ->getQuery()
            ->getResult()
            ;

Выдает ошибку:

[Syntax Error] line 0, col 44: Error: Expected Literal, got '"'

Не могу разобраться - так как правильно составить запрос с условием по полю json?

Answer 1

[Денис Дерепко]

Например

Comments

[HellWalk]

Поставил эту библиотеку. Пишу

$role = '["ROLE_COURIER"]';

        return $this->createQueryBuilder('user')
            ->where("JSON_GET_TEXT(user.roles, 'role') = :role")
            ->setParameter('role', $role)
            ->getQuery()
            ->getResult()
            ;

Ничего не находит. И ошибок нет.

[kristy_55]

try this:

return $this->createQueryBuilder('user')
->where("JSON_GET_TEXT(user.roles, 0) = :role")
->setParameter('role', $role)
->getQuery()
->getResult();

[Александр Афанасьев]

kristy_55, увы, JSON_GET_TEXT(user.roles, 0) берет первый элемент.

Т е если у юзера 2 роли, ROLE_FOO и ROLE_BAR?, то ROLE_FOO отфильтрует успешно(т к индекс этой роли в джсоне = 0), а ROLE_BAR - нет.

Как адекватно отфильтровать средствами доктрины я пока так и не понял