CODEWITHSUNDEEP
symfonydoctrine-orm
Patrick
Patrick

Reputation: 335

Doctrine 2 searching an array field with an array of options

I have the following query:

$roles = array();
        $roles[] = 'ROLE_SUPER_ADMIN';
        $roles[] = 'ROLE_ADMIN';
        $roles[] = 'ROLE_TRUSTEE';
        $roles[] = 'ROLE_MODERATOR';    

        $qb = $this->createQueryBuilder('u')
                    ->select('u')
                    ->where('u.username is not null')
                    ->andWhere('u.roles IN (:roles)')
                    ->setParameter('roles', $roles);

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

Which works. But this is not what I'm after. u.roles is an array field and can contain any number of 'roles'. I need to query that field to see if it contains any of the roles listed in the array defined by $roles. Essentially the reverse:

->andWhere('(:roles) IN u.roles')

But that throws an error.

Am I approaching this all wrong?

Upvotes: 5

Views: 6483

Answers (2)

Patrick
Patrick

Reputation: 335

Here's a better version

$roles = array();
        $roles[] = 'ROLE_SUPER_ADMIN';
        $roles[] = 'ROLE_ADMIN';
        $roles[] = 'ROLE_TRUSTEE';
        $roles[] = 'ROLE_MODERATOR'; 

        $ar = new ArrayCollection();
        $qb = $this->createQueryBuilder('u');
            $qb ->select('u');
        foreach($roles as $role){
            $qb->andWhere('u.roles LIKE :roles')
                ->setParameter('roles', '%"'.$role.'"%');          
        }
        $results = $qb->getQuery()->getResult();
        foreach ($results as $result){
            $ar[] = $result;
        }

        return $ar;

Upvotes: 5

Florian
Florian

Reputation: 875

I would try to implode the array into a string and use this string in the query

$implodedRoles = implode(',', $roles);

$qb = $this->createQueryBuilder('u')
                ->select('u')
                ->where('u.username is not null')
                ->andWhere('u.roles IN (:roles)')
                ->setParameter('roles', $implodedRoles);

Upvotes: 1

Related Questions