Question 113834 in PostgreSQL Question posted in
The official documentation can be found here.

Doctrine – postgresql, search in a json array

We are working with Symfony and Doctrine ORM. We mostly use MySQL / MariaDB, and for a new project we are considering trying PostgreSQL.

Obviously using an ORM like Doctrine is doing a lot of the work for us, and 99% of our requests are fine.

I’m stuck with a specific request, when I’d like to filter our users by their role. We normally do something like this, using LIKE:

public function findByRole(string $role): array
{
    return $this->createQueryBuilder('u')
        ->andWhere('u.roles LIKE :role')
        ->setParameter('role', '%"'.$role.'"%')
        ->orderBy('u.id', 'ASC')
        ->getQuery()
        ->getResult()
    ;
}

This is unfortunately not working with postgresql, and after searching on this forum I found a solution using JSON_GET_TEXT. But my problem is that we have to give the index of the array to search in, and I don’t know where my role could be.

So I have this somewhat working function right now, but it is so ugly :

/**
 * Search all the users having a specific role.
 *
 * @param string $role - The role to search for in user's roles
 *
 * @return mixed
 */
public function findByRole(string $role): mixed
{
    // TODO: Il y a probablement mieux à faire...
    return $this->createQueryBuilder('u')
        ->andWhere('JSON_GET_TEXT(u.roles,0) = :role')
        ->orWhere('JSON_GET_TEXT(u.roles,1) = :role')
        ->orWhere('JSON_GET_TEXT(u.roles,2) = :role')
        ->orWhere('JSON_GET_TEXT(u.roles,3) = :role')
        ->orWhere('JSON_GET_TEXT(u.roles,4) = :role')
        ->orWhere('JSON_GET_TEXT(u.roles,5) = :role')
        ->orWhere('JSON_GET_TEXT(u.roles,6) = :role')
        ->orWhere('JSON_GET_TEXT(u.roles,7) = :role')
        ->orWhere('JSON_GET_TEXT(u.roles,8) = :role')
        ->orWhere('JSON_GET_TEXT(u.roles,9) = :role')
            ->setParameter('role', $role)
        ->getQuery()
        ->getResult();
}

How can I improve my function to work on postgresql? And more importantly, is there a solution that could work on both MySQL and PostgreSQL with Doctrine so we could decide to switch seamlessly between the two database engines?

Tags:

3

Answers


  1. 0

    I don’t know if my answer is related with your problem.
    In PostgreSQL "user" is a reserved keyword, so you can’t create database named USER in postgre. But Symfony found a solution and switchs the name directly to ‘user’ with parentheses.
    What I would like to say is that you may face problems while using ‘User’ name in postgre so it’s recomended to name your table ‘account’ instead of ‘user’ here.

    Login or Signup to reply.
  2. 0

    Although there is at least one doctrine extension for the "transparent" usage of JSON functions within diffenent database engines, seamless switching in this case probably is not possible at the moment. There are too many differences in the flow of query execution depending on database engine, and even syntax is still far from being similiar.

    I believe there should happen some kind of standartization in the (far) future but the main problem it’s very out of scope in the context of "pure SQL". So I’m afraid you have to implement some wrapper for your purpose anyway.

    Login or Signup to reply.
  3. 0

    #symfony 6.* #postgresql #doctrine

    1. Try to use [martin-georgiev/postgresql-for-doctrine][1] bundle composer require martin-georgiev/postgresql-for-doctrine

    2. If the "roles" column can contain multiple roles for a single user, you may need to use the @> operator instead of ->> to check if the array contains the "ROLE_ADMIN" value. Here’s an example query for that scenario: My repo looks like this:

    {
    return $this->createQueryBuilder('u')
        ->andWhere('CONTAINS(TO_JSONB(u.roles), :role) = TRUE')
        ->setParameter('role', '["'.$role.'"]')
        ->orderBy('u.id', 'ASC')
        ->getQuery()
        ->getResult()
    ;
}```


  [1]: https://github.com/martin-georgiev/postgresql-for-doctrine
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search