In Symfony 4, I have a field where by typing part of the the username or
email and returns users from the database making an AJAX call.
Is working fine except I do not need users that have role ADMIN. So I
changed the query adding the 'u.roles NOT KIKE :role' and returns no
results.
If I remove the 2 following lines :
->andWhere('u.roles NOT LIKE :role')
->setParameter('role', '%ADMIN%')
the query is working fine.
Here is the query :
return $this->createQueryBuilder('u')
->andWhere('u.email LIKE :query OR u.username LIKE :query ')
->andWhere('u.roles NOT LIKE :role')
->setParameter('query', '%'.$query.'%')
->setParameter('role', '%ADMIN%')
->setMaxResults($limit)
->getQuery()
->getResult();
I dump at least one object from the database that should be matched :
'id' => string '8' (length=1)
'username' => string 'mike' (length=4)
'email' => string 'm...@m.com' (length=7)
'roles' => string '["ROLE_ADMIN","ROLE_USER"]' (length=26)
In the profiler I check the executed query :
SELECT u0_.id AS id_0, u0_.username AS username_1, u0_.email AS email_2,
> u0_.roles AS roles_3, u0_.password AS password_4, u0_.name AS name_5 FROM
> user u0_ WHERE (u0_.email LIKE '%mik%' OR u0_.username LIKE '%mik%') AND
> u0_.roles NOT LIKE '%ADMIN%' LIMIT 5;
I expect at least 1 result which corresponds to the provided row in my
database but nothing is returned and I cannot figure out what is wrong with
the query.
I also tried other ways to query, like using expr()->notLike() but with the
same results
$qb = $this->createQueryBuilder('u');
$users = $qb
->andWhere('u.email LIKE :query OR u.username LIKE :query')
->setParameter('query', '%'.$query.'%')
->andWhere(
$qb->expr()->notLike('u.roles', $qb->expr()->literal
('%ADMIN%'))
)
->setMaxResults($limit)
->getQuery()
->getResult();
return $users;
So Is it the query or something else is wrong ?
--
You received this message because you are subscribed to the Google Groups
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to doctrine-user+unsubscr...@googlegroups.com.
To post to this group, send email to doctrine-user@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.
