如何在Symfony与PostgreSQL中用CreateQueryBuilder查询指定角色用户

问题根源 #

你能正常运行的原生SQL用了PostgreSQL的jsonb @>操作符，检查数组是否包含指定的JSON字符串元素（比如'"ROLE_ADMIN"'），但你的Doctrine代码有两个核心问题：

• 传递的参数是纯字符串（ROLE_ADMIN），而非原生SQL要求的JSON格式字符串（"ROLE_ADMIN"）
• 使用的JSON_CONTAINS函数在PostgreSQL中的行为和你预期的原生@>操作符不匹配

解决方案 #

方案一：使用PostgreSQL原生@>操作符（推荐） #

直接复用你验证有效的原生SQL逻辑，在Doctrine查询构建器中使用原生操作符，并修正参数格式：

public function findAdministrators(int $offset): Paginator
{        
    $roles = ["ROLE_ADMIN", "ROLE_ADMIN_DELEGATION"];
    $qb = $this->createQueryBuilder('u');

    $orConditions = [];
    $parameters = [];

    foreach ($roles as $index => $role) {
        $paramName = 'role' . $index;
        // 使用PostgreSQL jsonb包含操作符，检查数组是否包含目标角色的JSON字符串
        $orConditions[] = "u.roles::jsonb @> :{$paramName}";
        // 将角色转为JSON格式字符串（自动添加双引号）
        $parameters[$paramName] = json_encode($role);
    }

    $qb->where(implode(' OR ', $orConditions))
        ->setParameters($parameters)
        ->setMaxResults(self::MEMBERS_PER_PAGE)
        ->setFirstResult($offset);

    return new Paginator($qb->getQuery());
}

优化：将字段改为jsonb类型 #

为了避免查询中手动转换类型（::jsonb），并提升查询性能，建议修改实体字段定义为jsonb类型：

/**
* @var list<string> The user roles     
*/
#[ORM\Column(type: 'jsonb')]     
private array $roles = [];

之后查询中的条件可以简化为：

$orConditions[] = "u.roles @> :{$paramName}";

方案二：使用Doctrine的JSON_ARRAY_CONTAINS函数 #

如果你的Doctrine ORM版本在2.10以上，可以使用官方提供的JSON_ARRAY_CONTAINS函数，它会自动适配PostgreSQL的@>操作符，无需手动处理JSON格式：

public function findAdministrators(int $offset): Paginator
{        
    $roles = ["ROLE_ADMIN", "ROLE_ADMIN_DELEGATION"];
    $qb = $this->createQueryBuilder('u');

    foreach ($roles as $index => $role) {
        $paramName = 'role' . $index;
        $qb->orWhere("JSON_ARRAY_CONTAINS(u.roles, :{$paramName})")
           ->setParameter($paramName, $role);
    }

    $qb->setMaxResults(self::MEMBERS_PER_PAGE)
       ->setFirstResult($offset);

    return new Paginator($qb->getQuery());
}

关键说明 #

• 原生SQL中@> '"ROLE_ADMIN"'的本质是检查jsonb数组是否包含JSON字符串元素，因此参数必须是带双引号的JSON格式，json_encode($role)会自动完成这个转换
• JSON_ARRAY_CONTAINS是Doctrine的跨数据库JSON函数，会根据底层数据库自动生成对应的SQL语法，兼容性更好

内容的提问来源于stack exchange，提问作者David Bermudez