php - Symfony Doctrine 查询生成器 LIKE 在 PostgreSQL 中不起作用

Question

所以我有一个包含以下代码的 UserRepository

namespace AppBundle\Repository;
use \Doctrine\ORM\EntityRepository;
class UserRepository extends EntityRepository
{
    public function findByRole($role)
    {
        $qb = $this->_em->createQueryBuilder();
        $qb->select('u')
            ->from($this->_entityName, 'u')
            ->where('u.roles LIKE :roles')
            ->setParameter('roles', '%"'.$role.'"%');

        return $qb->getQuery()->getResult();
    }
}

如果我的数据库是 MySQL，这似乎工作得很好，但如果我将数据库更改为 PostgreSQL，这个查询会抛出以下错误

An exception occurred while executing 'SELECT p0_.id AS id_0, p0_.username AS username_1, p0_.password AS password_2, p0_.is_active AS is_active_3, p0_.roles AS roles_4, p0_.name AS name_5, p0_.street AS street_6, p0_.city AS city_7, p0_.state AS state_8, p0_.zip_code AS zip_code_9, p0_.phone_number AS phone_number_10, p0_.dob AS dob_11, p0_.company_name AS company_name_12, p0_.company_slug AS company_slug_13, p0_.company_logo AS company_logo_14, p0_.company_details AS company_details_15, p0_.stripe_customer_id AS stripe_customer_id_16, p0_.created_at AS created_at_17, p0_.updated_at AS updated_at_18 FROM px_user p0_ WHERE p0_.roles LIKE ?' with params ["%\"ROLE_EMPLOYER\"%"]:

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json ~~ unknown LINE 1: ...at AS updated_at_18 FROM px_user p0_ WHERE p0_.roles LIKE $1 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

这是我第一次使用 PostgreSQL，所以我不明白问题出在哪里。如果我将生成的查询更改为添加以下内容，则在玩了一会儿之后

WHERE 
  p0_.roles::text LIKE '%ROLE_EMPLOYER%'

一切正常。注意 ::text。

那么现在我如何将它添加到查询构建器，以便它也能与 PostgreSQL 一起工作。

Answer 1

我用 boldtrn/jsonb-bundle 模块解决了这个问题，但它根据使用的 Postgres 版本产生了一个错误。

我还通过这样的 native 查询在没有模块的情况下解决了这个问题:

public function findEmailsByRole($role)
{
    return $this->_em->getConnection()->executeQuery(
        "SELECT email FROM public.utilisateur WHERE roles::text LIKE :role",
        ['role'=>'%"' . $role . '"%']
    )->fetchAll();
}