Doctrine 2 in Symfony 2 - Filtering a QueryBuilder by an association

Question

I have my two classes User and Role, and I need to make a QueryBuilder which returns a query for the users who have the ROLE_PROVIDER role. I need this for an entity form field in Symfony 2. In the Form Class definition I have the following snippet for the mentioned field:

$builder->add('provider', 'entity', array(
    'class'    => 'ElCuadreAccountBundle:User',
    'property' => 'username',
    'query_builder' => function(UserRepository $ur) {
                         return $ur->getUsersByRoleQB('ROLE_PROVIDER');
                       },
    'required' => true,
));

And then in my Custom UserRepository I have the following function, which should return a QueryBuilder object:

public function getUsersByRoleQB($role) {
    $qb = $this->createQueryBuilder('u');
    return $qb->join('u.roles','r')
              ->where($qb->expr()->in('r.role',$qb->expr()->literal($role)))
              ->orderBy('u.username', 'ASC');
}

Of course this doesn't work, but i pasted it to illustrate my needs.

I've been looking around and it seems Doctrine2 does not support natively filtering by an association. In this page they say so, and suggest using DQL for this kind of filtering. My problem is that I have not found how to make a QueryBuilder object from a DQL sentence. If you could also provide me with the right DQL query, I would be very grateful...

Thanks for your help!

Answer 1

where should actually do what you want. You just have the syntax wrong for 'in':

This

->where($qb->expr()->in('r.role',$qb->expr()->literal($role)))

Should be

->where($qb->expr()->in('r.role',$role))

I know it may seem a bit strange but since prepared statements do not directly support arrays, parameters to IN clauses always have to be escaped individually (which doctrine does for you). Hence the syntax is a bit different then say for an eq expression where literal is required.

You do raise a good question because I have needed to filter by association. I think D2.2 will allow this out of the box. I have not really tried it but I suspect that

$dql = 'a,b FROM whatever...'; // Don't start with SELECT
$qb->select($dql); 
return $qb;

will actually work without specifying any other parts as long as your leave the actual 'SELECT' string out of the $dql. Untested.

Answer 2

even simpler still you can do:

->where('r.role IN (:role)' )
->setParameter( 'role', $role );

I find this much more legible than adding the $qb->expr()...