隐性加入和在哪里教义 - 怎么样？ [英] Implicit joins and Where in Doctrine - how?

问题描述

我有一个ManyToMany用户和角色的关系。那就是我有用户表和实体类，角色表和实体以及连接表user_rolewidth user_id和role_id列。

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 ")

无论如何，感谢这个答案我向我的两个实体类添加了正确的映射（注释），然后删除了我自己的连接，让Doctrine完成这个工作：

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

这个工作。我有一个所有用户的列表，然后我可以访问他们的角色（感谢User-> getRoles（）方法）。

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

然而，现在我只想列出只有具有某些角色的用户，例如ROLE_ADMIN，我不知道该怎么做：

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')

顺便说一句，SQL代码生成按照Doctrine看起来像这样：

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_

所以那里是没有JOIN。从Doctrine文档我知道这被称为懒惰负载 - 某些用户的角色将按需提取。

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.

但是，我该怎么做：

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'

？

推荐答案

使用此选项：

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

我假设您将实体中的角色名称映射为属性roleName。

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

这篇关于隐性加入和在哪里教义 - 怎么样？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！