隐含联接和Doctrine中的位置 - 如何？

Question

I have a ManyToMany relation of Users and Roles. That is I have User table and entity class, Role table and entity and a joining table "user_role" width user_id and role_id columns.

Now, I recently tried to get users with their roles, by using joins, like this:

$qb = $this->createQueryBuilder('u')
    ->join('user_role', 'ur', Join::ON, "I didn't know what to put here, nothing worked ")

Anyway, thanks to this answer I added correct mapping (annotations) to my both entity classes and then removed my own join, letting Doctrine do the job:

$qb = $this->createQueryBuilder('u');
$q = $qb->getQuery();
$users = $q->getResult();

And this works. I have a list of all users, and then I can access their roles (thanks to User->getRoles() method).

However, now I want to list only users having certain roles, for example 'ROLE_ADMIN' and I have no idea how to do this:

$qb = $this->createQueryBuilder('u')
>where('what_to_put_here = :roles')
->setParameter('roles', 'what_to_put_here')

By the way, the SQL code generated by Doctrine looks like this:

SELECT u0_.id AS id_0, u0_.username AS username_1, u0_.personal_name AS personal_name_2, u0_.password AS password_3, u0_.email AS email_4, u0_.is_active AS is_active_5 FROM user u0_

So there is no JOIN. From the Doctrine docs I know this is called "lazy load" - the roles of certain user will be fetched on demand.

But then, how can I do something like this:

SELECT * FROM `user` 
JOIN user_role on user_role.user_id = user.id
JOIN role on role.id = user_role.role_id
WHERE role.role_name = 'ROLE_ADMIN'

?

Answer 1

Use this:

$qb = $this->createQueryBuilder('u')
    ->innerJoin('u.roles', 'r')
    ->where('r.roleName = :roleNameParameter')
    ->setParameter('roleNameParameter', 'ROLE_ADMIN');

I'm assuming you mapped the column role_name as property roleName in the entity.