Pageable not giving expected results with @ManyToMany relationship

Question

We are dealing with @ManyToMany relation with Users and Roles and want to have pagination to get all the Users with associated Roles by using Pageable interface. It is only considering the records count for pagination on the User Table and Roles table record is not considered. But ideally in RDBMS the actual record count would be after flattening the result of join between Users and Roles table.

When working with Pageable in findAll method and passing the page configuration as below :

pageno: 0 and pageSize:1

    Pageable paging = PageRequest.of(0, 1);
    userRepository.findAll(paging);

It is giving the result as below 

Technically there are 3 records when we flatten the result but pageable is considering this as 1 record which is not correct. Is this intended behavior?

Is there a way where we can get the pagination after flattening the result set of query?

Answer 1

Yes. This is intended. Data is mapped to Java objects as nested objects. Hence, pageable of 5 user records will return 5 users irrespective of number of roles each user has.

To restrict pagination based on record count by combination of user and role, you have to add join between user and role to the query in repository method and fetch columns from both user and role (like we do in SQL).

Below code works for me

User entity

public class User
{

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long userId;
    
    @NonNull
    @Column(unique = true, name= "user_name")
    private String userName;
    
    @NonNull
    private String password;
    
    @NonNull
    private boolean status;
    
    @NonNull
    private boolean passwordExpired;
    
    
    @ManyToMany(fetch=FetchType.EAGER,cascade = CascadeType.ALL)
    @JoinTable(name = "user_role", joinColumns = {
            @JoinColumn(name = "userId", referencedColumnName = "userId") }, inverseJoinColumns = {
                    @JoinColumn(name = "role_name", referencedColumnName = "name") })

    @BatchSize(size = 20)
    private Set<Role> roles = new HashSet<>();
//Get and set
}

Role Entity

public class Role  {

    private static final long serialVersionUID = 1L;

    @NotNull
    @Size(max = 50)
    @Id
    @Column(length = 50,unique=true)
    private String name;
//get and set
    
}

Repository

@Repository
public interface UserRepo extends JpaRepository<User, Long>
{   
    @Query(value="SELECT u.userName,r.name FROM User u left join u.roles r")
    public ArrayList<User> findByrole(Pageable paging);
}

Service method

public ArrayList<User> findByrole() 
    {
        // TODO Auto-generated method stub
        Pageable paging = PageRequest.of(0, 4);
        return uRepo.findByrole(paging);
    }