如何在Doctrine中将多连接SQL转换为Query Builder/DQL查询

你现有编写的代码存在3个核心错误，修正后即可实现和原生SQL完全一致的查询逻辑：

• 查询字段不匹配：原生SQL需要返回成员ID、角色名、权限动作三个字段，你当前只查询了tm实体，且别名和from语句定义的wm不一致
• 关联逻辑缺失：原生SQL中团队成员和角色的关联条件为TM.TEAM_ID = R.TEAM_ID，你需要在join时补充该匹配条件（如果你的many-to-many中间表已经保证关联的角色属于同团队，可省略该条件）
• 多余且错误的过滤条件：你写的r.role = :id、ra.ability = :roleId对应字段在你提供的实体映射中不存在，且原生SQL无该过滤逻辑，需要删除

可直接使用的查询代码 #

// 基础查询，和你提供的原生SQL逻辑完全一致
$results = $this->entityManager->createQueryBuilder()
    ->select('IDENTITY(tm.member) as member_id, r.name as name, ra.action as abilities')
    ->from('App:TeamMember', 'tm')
    // 如果你的实体类名是WorkspaceMember，把上面from的参数替换为'App:WorkspaceMember'即可
    ->innerJoin(
        'tm.teamMemberRoles', 
        'r', 
        'WITH', 
        'tm.team = r.team' // 匹配原生SQL中TM.TEAM_ID = R.TEAM_ID的关联规则
    )
    ->innerJoin('r.roleAbilities', 'ra') // 该关联已在实体映射中配置，自动匹配R.ID = RA.ROLE_ID
    // 如果需要按指定团队ID过滤，取消下面两行注释即可
    // ->where('tm.team = :teamId')
    // ->setParameter('teamId', $targetTeamId)
    ->getQuery()
    ->getArrayResult();

查询结果格式化 #

DQL默认返回平铺的行结构，和你原生SQL直接返回的结构一致，如果需要得到你示例中同成员、同角色下合并权限列表的结构，在PHP层遍历处理即可：

$formattedResult = [];
foreach ($results as $row) {
    $groupKey = $row['member_id'] . '_' . $row['name'];
    if (!isset($formattedResult[$groupKey])) {
        $formattedResult[$groupKey] = [
            'member_id' => $row['member_id'],
            'name' => $row['name'],
            'abilities' => []
        ];
    }
    $formattedResult[$groupKey]['abilities'][] = $row['abilities'];
}
// 重置索引返回标准列表
$finalResult = array_values($formattedResult);

注意：如果你的TeamMember和Role之间的many-to-many关联已经通过中间表严格约束了关联角色必须属于当前成员所在团队，可以去掉innerJoin中的WITH条件段，直接写->innerJoin('tm.teamMemberRoles', 'r')即可，查询性能会更好。

内容的提问来源于stack exchange，提问作者yaraw69