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?

You are calling the `userRepository`. As far as `userRepository` is concerned, one `user` is `one` count. May be you want to create a `UserRole` entity and `UserRoleRepository` and it will give you the flatten record and it will count this as 3 records — Kavithakaran Kanapathippillai, Jul 23 '20 at 17:54
Thanks @KavithakaranKanapathippillai for the very quick response, suggestion and for editing .Will try this approach — Manoj Kumar, Jul 23 '20 at 18:21

Sridhar Patnaik · Accepted Answer · 2020-07-23T18:22:06.173

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);
    }

Thanks @sridhar For the quick response and sample example with explanations .. Will try this approach ... — Manoj Kumar, Jul 23 '20 at 18:26
@ManojKumar let me know if it solved your problem. Also, mark it as answer if this actually solved your problem — Sridhar Patnaik, Jul 24 '20 at 05:12
Yes Sridhar , it has really helped me to think forward and achieve the solution. I was little bit in confusion, to decide whether to use join or create a UserRole Join Table repository as suggested by Kavithakaran Kanapathippillai above.. Any way It helped me a lot and I am marking it as answer.. Thanks for the suggestions — Manoj Kumar, Jul 24 '20 at 06:34

Pageable not giving expected results with @ManyToMany relationship

1 Answers1