Pageable not giving expected results with @ManyToMany relationship [DATAJPA-1760]

[spring-projects-issues]

Manoj Kumar opened DATAJPA-1760 and commented

When 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. 

For Example:

Users Table

UserId	UserName
U1	John
U2	Mike

Role Table

RoleId	Name
R1	Admin
R2	Sales
R3	IT
R3	Ops

UserRole Table

 

RoleId	UserId
R1	U1
R2	U1
R3	U1
R3	U2

 

Result of Join between User and Roles Table 

 

UserId	UserName	RuleId	RuleName
U1	John	R1	admin
U1	John	R2	Sales
U1	John	R3	IT
U2	Mike	R4	Ops

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 

!image-2020-07-23-12-55-16-479.png!

Here Technically there are 3 records when we flatten the result but pageable is considering this as 1 record which is not correct . kindly suggest if this is intended behavior, if yes then 

does there is any way where we can get the pagination after flattening the result set of query.

 

 

 

 

 

---

Attachments:

• image-2020-07-23-12-55-16-479.png (14.88 kB)

[spring-projects-issues]

Jens Schauder commented

You are not requesting (User, Roles) tuples, but User instances. Therefore I consider the current behaviour correct

[spring-projects-issues]

Manoj Kumar commented

Hi Jens Schauder Thanks for the quick response ..

Could you please provide some guidance here, i.e how we can request  (User, Roles) tuples to get the correct records and count in this type of situations, by some example or link to any documentation that would be a great help..

Thank you ... 

 

[spring-projects-issues]

Jens Schauder commented

I can't help with the Spring Data Rest part of this. That is the part that turns query results into JSON responses.

On a Spring Data JPA side something similar to the following should work, I think:

@Query(query="select u, r from User u join u.roles r")
Page<Object[]> paginatedTuples(Pageable p)

If you need more help with this please use Stackoverflow to ask your questions