How to search an array of an entity in dql?

Question

I am trying to filter out admins that have a super admin role. Why is the following not working?

public function findAdmins()
{
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb
        ->select('a')
        ->from('MyBundle:Admin', 'a')
        ->where($qb->expr()->notIn('ROLE_SUPER_ADMIN', 'a.roles'));

    $result = $qb->getQuery()->execute();

    return $result;
}

It will give me the following error:

[Syntax Error] line 0, col 68: Error: Expected Literal, got 'a'

The DQL-query looks like this:

SELECT a FROM MyBundle:Admin a WHERE ROLE_SUPER_ADMIN NOT IN(a.roles)

Role itself is not an entity. It is simply an array of strings.

$roles = array('ROLE_ADMIN', 'ROLE_SUPER_ADMIN)'

is `a.roles` a collection you are joining? – prodigitalson Jul 29 '13 at 04:10 — prodigitalson, Jul 29 '13 at 04:10

score 2 · Accepted Answer · edited May 23 '17 at 12:02

2

Try use MEMBER OF operator

$qb
->select('a')
->from('MyBundle:Admin', 'a')
->where(':role NOT MEMBER OF ad.roles')->setParamete('role', 'ROLE_SUPER_ADMIN');

Update. The only solution found is to check serialized string in db with LIKE. Details here

edited May 23 '17 at 12:02

Community

1
1

answered Jul 29 '13 at 06:24

Alexey B.

11,965
2
49
73

This is giving me the following error: [Semantical Error] line 0, col 65 near 'roles': Error: Invalid PathExpression. Must be a CollectionValuedAssociationField. – xfscrypt Jul 30 '13 at 03:25
look at this question http://stackoverflow.com/questions/9016914/symfony-2-fos-bundle-how-to-select-users-with-a-specific-role – Alexey B. Jul 30 '13 at 04:29

score 0 · Answer 2 · answered Jul 29 '13 at 04:40

0

Don't mix column name with table name

Try this,

$qb
    ->select('a')
    ->from('MyBundle:Admin', 'ad')
    ->where($qb->expr()->notIn('ROLE_SUPER_ADMIN', 'ad.roles'));

It should be like,

SELECT a FROM MyBundle:Admin ad WHERE ROLE_SUPER_ADMIN NOT IN(ad.roles)

answered Jul 29 '13 at 04:40

Rohan Kumar

40,431
11
76
106

The same error comes up: [Syntax Error] line 0, col 62: Error: Expected Literal, got 'ad' – xfscrypt Jul 30 '13 at 03:16

How to search an array of an entity in dql?

2 Answers2