Doctrine & Symfony - Get data from database with parameter on array column

Question

Good afternoon,

I try to find, from my database specific users with a role which is passed in parameter to the query. However, I have "null" result currently.

The users class is like this example from Symfony Documentation.

This is my CustomerRepository:

class CustomerRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Customer::class);
    }

    public function findByRole(string $role)
    {
        return $this->getEntityManager()->createQuery(
            'SELECT c
             FROM App\Entity\Customer c
              WHERE c.roles IN (:role)'
        )
            ->setParameter(':role', $role)
            ->getResult()
        ;
    } 
}

I expect to return an array which contains one or several users who have the role.

score 0 · Answer 1 · answered Feb 14 '20 at 17:14

0

c.roles is a list that can contains multiple items (roles), so please can you reverse the where like this :

WHERE :role IN c.roles

PS: insure the case of string $role is upper (e.g "ROLE_ADMIN").

answered Feb 14 '20 at 17:14

Lounis

597
7
15

score 0 · Answer 2 · answered Feb 14 '20 at 21:22

First of all in the class from the example you have given does not have declared variable called roles, therefore you cannot write this

 ... WHERE c.roles IN (:role)'

So in your User class you should have something like this

private $roles;

Also as it said here you can have "multiple tables and JOIN them in your queries."

So you would have 1 table for all your roles, one for the users and one called user_roles where you will store the roles for each user. The next step I would approach is to search this new table for the user I want and then retrieve all rows containing the different roles in an array.

Also there are other ways - stated here. The method I recommend when storing arrays is to have a VARCHAR field (or TEXT) and then use (in your case) the Symfony serializer component to serialize() the array when saving in the DB and to deserialize()it when needed.

The Customer class contains an array type for $roles variable & in Doctrine, it is an array. No table has been added for this. — nboulfroy, Feb 17 '20 at 09:25
Ok.. So in the doctrine docs the array type is described as following - _Maps and converts array data based on PHP serialization._ and _This type will always be mapped to the database vendor's text type internally as there is no way of storing a PHP array_. So as I said before with few changes - doctrine saves a serialized array so you can use PHP deserialize to convert the data to array and then check if the value exists. Another way is using regex in where statement - `'SELECT c FROM App\Entity\Customer c WHERE field REGEXP '.*"array_key";s:[0-9]+:".*array_value.*".*' ` — Slavian, Feb 17 '20 at 10:01

score 0 · Accepted Answer · answered Feb 19 '20 at 10:41

Good evening,

Thx for your help.

To resolve this problem, I considered that a User have only one role in application, in this context, the solution is like this:

public function findByRole(string $role, int $isActive = 1): ?Customer
{
    return $this->getEntityManager()->createQuery(
            'SELECT c
             FROM App\Entity\Cutomer c
             WHERE c.roles LIKE :role
             AND c.isActive = :isActive'
        )
        ->setParameters([
             ':role' => '%'.$role.'%',
             ':isActive' => $isActive,
        ])
        ->getOneOrNullResult()
    ;
}

Doctrine & Symfony - Get data from database with parameter on array column

3 Answers3