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!
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.