Doctrine findBy type="json"

Question

Can I query db by roles?

In database i have column roles type json and store data like this ["ROLE_ADMIN"]

How can i query via doctrine? I use this code down and don't work.

$user = $this->getDoctrine()
            ->getRepository(User::class)
            ->findBy(['roles' => 'ROLE_ADMIN');

Possible duplicate of [FOS bundle - How to select users with a specific role?](https://stackoverflow.com/questions/9016914/fos-bundle-how-to-select-users-with-a-specific-role) — Yassine CHABLI, Apr 03 '19 at 16:36
https://stackoverflow.com/questions/47776983/how-make-doctrine-findby-to-json-field-without-native-query — Zeljka, Apr 03 '19 at 21:26

score 1 · Accepted Answer · answered Apr 03 '19 at 14:46

In your UserRepository create a method which uses a like statement to find a user by role. Like this for example:

class UserRepository extends EntityRepository
{
    public function findUsersByRole($role)
    {
        $qb = $this->createQueryBuilder('u');
        $qb->select('u')
            ->where('u.roles LIKE :roles')
            ->setParameter('roles', '%"'.$role.'"%');

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

Doctrine findBy type="json"

1 Answers1