Blazebit
PageAndNavigate method does not add row position in main query criteria
###Description I am using pageAndNavigate method provided in the PaginatedCriteriaBuilder interface to paginate the resultlist based on entity identifier. The count query generated has the page position for the entity id determined correctly. However, the main query only includes the limit in the query criteria.
Expected behavior
When I provide an entity identifier, the results should be fetched after the row number of this entity identifier Or atleast the page having the id should be rendered starting frm the first item on the page. The row position of the identifier should be included in the main query and records should be fetched after that row position.
Actual behavior
The main query does not have the correct rownum clause and fetches results from the first record itself. Providing the entity id to the method has no impact on the results.Three pages of data have to fetched due to this behaviour.
Steps to reproduce
Database data for the criteria in query EmpId EmpId10 EmpId11 EmpId12 EmpId13 EmpId14 EmpId15 EmpId16 EmpId17 EmpId18 EmpId19 EmpId20 EmpId21
Code snippet //Fetches the first page only although first result is set //to the first item of the page where entity is present PagedList<Employee> page1= cbf.create(em, Employee.class, "emp") .innerJoin("department", "dept") .where("dept.deptName").eq("Accounting") .orderByAsc("emp.creatTs") .orderByAsc("emp.empId") .pageAndNavigate("EmpId16", 5) .withKeysetExtraction(true) .getResultlist();
//Page 2 will fetch the page having the entity id. PagedList<Employee> page2= cbf.create(em, Employee.class, "emp") .innerJoin("department", "dept") .where("dept.deptName").eq("Accounting") .orderByAsc("emp.creatTs") .orderByAsc("emp.empId") .page(page1.getFirstResult(), 5) .getResultlist();
//Final Page //The Id is filtered from the entity id and it's index is //fetched Int finalPageFirstResult= 0 Employee emp=page2.stream.filter(e->e.getEmpId().equals("EmpId16")).findFirst();
finalPageFirstResult= page2.indexOf(emp)+page1.getFirstResult();
PagedList<Employee> empList= cbf.create(em, Employee.class, "emp") .innerJoin("department", "dept") .where("dept.deptName").eq("Accounting") .orderByAsc("emp.creatTs") .orderByAsc("emp.empId") .page(finalPageFirstResult ,5) .getResultlist();
Expected Result Sequence Page starting from row position after Empid16 should be returned with the first query having pageAndNavigate itself. Subsequent queries to fetch the entity page and then the page from entity row position can be avoided EmpId EmpId17 EmpId18 EmpId19 EmpId20 EmpId21 Or atleast page having the entity id EmpId16 EmpId Empid15 EmpId16 EmpId17 EmpId18 EmpId19
Actual Result Sequence of Data EmpId EmpId10 EmpId11 EmpId12 EmpId13 EmpId14
The Id provided in pagination method - pageAndNavigate is ignored and the data from first page is rendered. Can there be a new method added that navigates to the row position of the entity id , renders records starting after the row position of the entity id and sets the row position of the identifier in the firstResult field of the PagedList. Atleast, current pageAndNavigate should return the page having the entity id and also return the row position of the entityid so that it can be used in the subsequent page.
Environment
Blaze Persistence Version: 1.6.9 Spring Boot Version: 2.7.0 JPA-Provider: Hibernate 5.6.9 Final DBMS: Oracle 19c Application Server: Java SE 1.8
The method pageAndNavigate tries to load the page on which an entity is. If the ordering (creatTs,empId) produces a list like this:
EmpId10
EmpId11
EmpId12
EmpId13
EmpId14
EmpId15
EmpId16
EmpId17
EmpId18
EmpId19
EmpId20
EmpId21
Then the call to pageAndNavigate("Emp16Id", 5) should return you page 2 i.e.:
EmpId15
EmpId16
EmpId17
EmpId18
EmpId19
If it doesn't return you page 2, that would be a bug.
The Id provided in pagination method - pageAndNavigate is ignored and the data from first page is rendered.
Sounds like this is a bug then. I'll try to reproduce it in the testsuite.
Can there be a new method added that navigates to the row position of the entity id , renders records starting after the row position of the entity id and sets the row position of the identifier in the firstResult field of the PagedList. Atleast, current pageAndNavigate should return the page having the entity id and also return the row position of the entityid so that it can be used in the subsequent page.
It sounds to me like you would be much happier with keyset paging/scrolling. See https://persistence.blazebit.com/documentation/1.6/core/manual/en_US/#keyset-pagination-support
###Description I am using pageAndNavigate method provided in the PaginatedCriteriaBuilder interface to paginate the resultlist based on entity identifier. The count query generated has the page position for the entity id determined correctly. However, the main query only includes the limit in the query criteria.
Expected behavior
When I provide an entity identifier, the results should be fetched after the row number of this entity identifier Or atleast the page having the id should be rendered starting frm the first item on the page. The row position of the identifier should be included in the main query and records should be fetched after that row position.
Actual behavior
The main query does not have the correct rownum clause and fetches results from the first record itself. Providing the entity id to the method has no impact on the results.Three pages of data have to fetched due to this behaviour.
Steps to reproduce
Database data for the criteria in query EmpId EmpId10 EmpId11 EmpId12 EmpId13 EmpId14 EmpId15 EmpId16 EmpId17 EmpId18 EmpId19 EmpId20 EmpId21
Code snippet //Fetches the first page only although first result is set //to the first item of the page where entity is present PagedList page1= cbf.create(em, Employee.class, "emp") .innerJoin("department", "dept") .where("dept.deptName").eq("Accounting") .orderByAsc("emp.creatTs") .orderByAsc("emp.empId") .pageAndNavigate("EmpId16", 5) .withKeysetExtraction(true) .getResultlist();
//Page 2 will fetch the page having the entity id. PagedList page2= cbf.create(em, Employee.class, "emp") .innerJoin("department", "dept") .where("dept.deptName").eq("Accounting") .orderByAsc("emp.creatTs") .orderByAsc("emp.empId") .page(page1.getFirstResult(), 5) .getResultlist();
//Final Page //The Id is filtered from the entity id and it's index is //fetched Int finalPageFirstResult= 0 Employee emp=page2.stream.filter(e->e.getEmpId().equals("EmpId16")).findFirst();
finalPageFirstResult= page2.indexOf(emp)+page1.getFirstResult();
PagedList empList= cbf.create(em, Employee.class, "emp") .innerJoin("department", "dept") .where("dept.deptName").eq("Accounting") .orderByAsc("emp.creatTs") .orderByAsc("emp.empId") .page(finalPageFirstResult ,5) .getResultlist();
Expected Result Sequence Page starting from row position after Empid16 should be returned with the first query having pageAndNavigate itself. Subsequent queries to fetch the entity page and then the page from entity row position can be avoided EmpId EmpId17 EmpId18 EmpId19 EmpId20 EmpId21 Or atleast page having the entity id EmpId16 EmpId Empid15 EmpId16 EmpId17 EmpId18 EmpId19
Actual Result Sequence of Data EmpId EmpId10 EmpId11 EmpId12 EmpId13 EmpId14
The Id provided in pagination method - pageAndNavigate is ignored and the data from first page is rendered. Can there be a new method added that navigates to the row position of the entity id , renders records starting after the row position of the entity id and sets the row position of the identifier in the firstResult field of the PagedList. Atleast, current pageAndNavigate should return the page having the entity id and also return the row position of the entityid so that it can be used in the subsequent page.
Environment
Blaze Persistence Version: 1.6.9 Spring Boot Version: 2.7.0 JPA-Provider: Hibernate 5.6.9 Final DBMS: Oracle 19c Application Server: Java SE 1.8
Thanks @beikov for acknowledging the bug. By when is 1.6.10 version going to be released?
There is no ETA and first I have to reproduce the bug ;) I'm currently on vacation, so unless someone steps in to provide a test and fix, I won't really spend time on this for now. I'll take a quick look on Monday to see how hard this is to fix.
@beikov , I have reviewed the keyset pagination mechanism already. Is there a way the keyset page of the entity id can be set so that records after the keyset can be fetched. My usecase has complex sorting rules and the order by clause is dynamic. But I do get a token ie entity id in my search request that I have to use to paginate my search results. As shown in the code, I have employed offset pagination to do the same but since it's going to be 3 queries at the moment and with the pageAndNavigate method not returning the entity page, this is not a very efficient solution. I want to utilise the keyset of the entity id so that my search performance is improved. Appreciate any help!!
You could do something like this:
PagedList empList= cbf.create(em, Employee.class, "emp")
.innerJoin("department", "dept")
.leftJoin(Employee.class, "employeeToFind").on("employeeToFind").eq(employeeId)
.where("dept.deptName").eq("Accounting")
.orderByAsc("emp.creatTs")
.orderByAsc("emp.empId")
.whereOr()
.where("emp.creatTs").gtExpression("employeeToFind.creatTs")
.whereAnd()
.where("emp.creatTs").eqExpression("employeeToFind.creatTs")
.where("emp.empId").gtExpression("employeeToFind.empId")
.endAnd()
.endOr()
.page(0, 5)
.getResultlist();
This will fetch the first 5 employees after the employee with the given id, according to the sorting rule. The next query can then use keyset pagination.
PagedList empList= cbf.create(em, Employee.class, "emp")
.innerJoin("department", "dept")
.where("dept.deptName").eq("Accounting")
.orderByAsc("emp.creatTs")
.orderByAsc("emp.empId")
.page(keysetPage, keysetPage.getOffset() + 5, 5)
.getResultlist();