I have a Users endpoint to get all the users in MySQL sorted by a boolean value using pagination, as the number keeps growing I found out it's skipping some users and duplicating others here's some screenshots in postman where a user with the same id (95) is duplicating in 2 different pages

Also sometimes i can't find some users in all the pages

Page 2

Page 3

ServiceImpl

@Override
    public PagedResponse<?> findAll(UserPrincipal currentUser, int page, int size) {
        AppUtils.validatePageNumberAndSize(page, size);
//return users with 'isBoard' true first
        Pageable pageable = PageRequest.of(page, size, Sort.Direction.DESC, "isBoard");
        if (currentUser.getAuthorities().contains(new SimpleGrantedAuthority(RoleName.ROLE_ADMIN.toString()))
                || currentUser.getAuthorities().contains(new SimpleGrantedAuthority(RoleName.ROLE_SUPER_ADMIN.toString()))) {
            Page<MEUser> contentPage = userRepository.findAll(pageable);
            List<MEUser> list = contentPage.getNumberOfElements() == 0 ? Collections.emptyList() : contentPage.getContent();
            return new PagedResponse<>(list.stream()
                    .map(user -> mapToAllUsersDTO(user, new AllUserDTO()))
                    .collect(Collectors.toList()), contentPage.getNumber(), contentPage.getSize(), contentPage.getTotalElements(), contentPage.getTotalPages(), contentPage.isLast());
        } else {
            Page<MEUser> contentPage = userRepository.findByMemberTrue(pageable);
            List<MEUser> list = contentPage.getNumberOfElements() == 0 ? Collections.emptyList() : contentPage.getContent();
            return new PagedResponse<>(list.stream()
                    .map(user -> mapToAllUsersDTO(user, new AllUserDTO()))
                    .collect(Collectors.toList()), contentPage.getNumber(), contentPage.getSize(), contentPage.getTotalElements(), contentPage.getTotalPages(), contentPage.isLast());
        }
    }

CodePudding user response：

This is a database problem rather than in your code. The database does not ensure consistent results when there's multiple rows with the same value of an ordered field.

When using pagination with LIMIT and OFFSET you have to supply an order by field with unique values, so in your case you have to sort by isBoard and id to ensure consistent result.

CodePudding user response：

This is because the if condition is validating only ADMIN and SUPER_USER. user 95 may have other roles that come in else conditions. Please check once and provide the database info for Roles and Users.

CodePudding user response：

i added the id condition to the pagination:

Pageable pageable = 
  PageRequest.of(page, size, Sort.by("isBoard").descending().and(Sort.by("id")));