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!

At

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.

Hydride

Thanks! really complete answer... One question, it's not very related with the original question, but here I go... I have read several times that new versions of symfony 2 and doctrine 2 are coming, and people seems to have knowledge of the new features, but i'm tired of running php bin/vendors install, and it doesn't upgrade neither symfony nor doctrine... how can I upgrade? or when would those new versions going to be official??? thanks for your answer!!! – At

You can read up about D2.2 here: doctrine-project.org . S2 will not have D2.2 integrated until S2.1 is released, probably this summer. You can however install D2.2 directly, adjust your paths in autoload.php and, for the most part, your doctrine code should work. Check the docs for things to watch out for. – Hydride

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()...

Lotte