PHP 从多对多获取

Question

我对这个 PHP 函数有疑问，我正在尝试获取用户及其角色和组，它对角色很好用，但我对组有问题，因为我得到的数组具有相同的组，当然还有我想要的是重复的自由数组。我明白为什么我会得到这个，因为我的用户有 2 个角色，我在 sql 查询中得到他 2 次，任何人都可以解释我什么是最佳实践以及如何避免这种情况，我应该使用这样的查询，还是我可以使用另一个查询，我将在其中找到成员的所有组，然后将其放在该数组中，但这是一个函数中的 2 个数据库调用，它可能不是很好的解决方案。

function getAll($mysqli) {
    $stmt = $mysqli->prepare("SELECT u.id, u.firstName, u.lastName, u.email, 
        u.phoneNumber, u.address, u.birthDate, ur.roleName, cg.id, cg.name FROM users as u 
        LEFT OUTER JOIN user_role as ur ON u.id = ur.userId 
        LEFT OUTER JOIN user_group as ug on ug.userId = u.id 
        LEFT OUTER JOIN control_group as cg on cg.id = ug.groupId");

    $stmt->execute();
    $stmt->bind_result($id, $firstName, $lastName, $email, $phoneNumber, 
        $address, $birthDate, $roleName, $groupId, $groupName);
    $users = array();

    while ($stmt->fetch()) {
        if (empty($users[$id])) {
            $users[$id] = array(
                'id' => $id,
                'firstName' => $firstName,
                'lastName' => $lastName,
                'email' => $email,
                'phoneNumber' => $phoneNumber,
                'address' => $address,
                'birthDate' => $birthDate,
                'roles' => array(),
                'groups' => array()
            );
        }
        if ($roleName) {
            $users[$id]['roles'][] = array(
                'roleName' => $roleName
            );
        }
        if ($groupId) {
            $users[$id]['groups'][] = array(
                'groupName' => $groupName
            );
        }
    }

    $stmt->close();
    $mysqli->close();;
    echo json_encode($users);
}

当我执行该函数时，我得到这样的响应

{  
   "1":{  
      "id":1,
      "firstName":"John",
      "lastName":"Doe",
      "email":"john@email.com",
      "phoneNumber":"062-441234-123123",
      "address":"Some Address 40\/2",
      "birthDate":"1989-12-29",
      "roles":[  
         {  
            "roleName":"Admin"
         },
         {  
            "roleName":"User"
         }
      ],
      "groups":[  
         {  
            "groupName":"Group 1"
         },
         {  
            "groupName":"Group 1"
         }
      ]
   },
   "2":{  
      "id":2,
      "firstName":"Jane",
      "lastName":"Doe",
      "email":"jane@email.com",
      "phoneNumber":"0112-11121-221322",
      "address":"Address 21ca",
      "birthDate":"1975-12-28",
      "roles":[  
         {  
            "roleName":"Admin"
         },
         {  
            "roleName":"User"
         }
      ],
      "groups":[  

      ]
   }
}

Answer 1

您可以在将角色(或组)插入数组之前检查它是否已经存在:

if ($roleName) {
    $found = false;
    foreach ($users[$id]['roles'] as $role) {
        if($role['roleName'] == $roleName){
            $found = true;
            break;
        }
    }
    if($found == false)
        $users[$id]['roles'][] = array(
            'roleName' => $roleName
        );
 }

请注意，我根据您的结构给了您一个答案。但我强烈建议更改数组的结构，以使检查角色是否存在的速度与 O(1)

一样快

编辑:为了完成我的回答:我会说 JSON 结构没有反射(reflect)真实情况。您声明用户具有角色和组，这是真的，但角色属于组。用户可以是组 1 中的管理员和组 2 中的任何人。这就是为什么我认为您真正应该输出的是这样的:

{  
   "1":{  
      "id":1,
      "firstName":"John",
      "lastName":"Doe",
      "email":"john@email.com",
      "phoneNumber":"062-441234-123123",
      "address":"Some Address 40\/2",
      "birthDate":"1989-12-29",
      "groups":[  
         {  
            "groupName": "Group 1",
            "roles": [
                {  
                    "roleName":"Admin"
                },
                {  
                    "roleName":"User"
                }
            ]
         }

      ]
   },
   "2":{  
      "id":2,
      "firstName":"Jane",
      "lastName":"Doe",
      "email":"jane@email.com",
      "phoneNumber":"0112-11121-221322",
      "address":"Address 21ca",
      "birthDate":"1975-12-28",
      "groups":[  

      ]
   }
}