Paginate query with multiple joins

Question

I have query with few LEFT OUTER JOINS, as you can see in code i generate object that contains 2 objects populated with arrays, as you know i can't use "simple" pagination here because for one user i get few columns (for every role and every group) so for example for user 1 i can get 4-5 results because user can have many groups and roles and i understand that is main reason why i cant in this query use LIMIT and offset because i would lose some info. My question is, how to modify this query in way that i get 20 different users per page and ALL their roles and groups.

$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 WHERE u.id != ?");
$stmt->bind_param("i", $_SESSION["id"]);
$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) {
        $found = false;
        foreach ($users[$id]['roles'] as $role) {
            if($role['roleName'] == $roleName){
                $found = true;
                break;
            }
        }
        if($found == false)
            $users[$id]['roles'][] = array(
                'roleName' => $roleName
            );
     }

    if ($groupId) {
        $found = false;
        foreach ($users[$id]['groups'] as $group) {
            if($group['groupName'] == $groupName){
                $found = true;
                break;
            }
        }
        if($found == false)
            $users[$id]['groups'][] = array(
                'groupName' => $groupName
            );
     }
}

$res = []; 

foreach($users as $user) { 
    $res[] = $user; 
} 

$stmt->close();
$mysqli->close();
echo json_encode($res);

Answer 1

Well, I think you could go for two queries approach. With first one you do pagination

SELECT u.id FROM users LIMIT offset, count

and second one is one that you have with u.id = (I think it should be =, not != as you have) changed to u.id IN

Smth like that

$stmt = $mysqli->prepare("SELECT u.id from users u limit ?, ?");
$stmt->bind_param("1", $_SESSION["offset"]);
$stmt->bind_param("2", $_SESSION["limit"]);
$stmt->execute();
$stmt->bind_result($id);

$user_ids = array();

while ($stmt->fetch()) {
    $user_ids[] = $id;
}

$stmt2 = $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 WHERE u.id in (?)");
$stmt2->bind_param("1", implode(",", user_ids);
$stmt2->execute();
$stmt2->bind_result($id, $firstName, $lastName, $email, $phoneNumber, 
    $address, $birthDate, $roleName, $groupId, $groupName);

$users = array();

while ($stmt->fetch()) {
    ...
}