Scrollable ResultSet Pagination Sample
Hi everyone.
This is a full example how to use a %ScrollableResultSet for results pagination using %DynamicQuery:SQL and build a JSON response including page details.
Feel free to use, share, comment or improve it.
Kind Regards
ClassMethod getPersonsPag(iAge As %Integer, sortField As %String = 1, sortOrder As %String = 2, pageSize As %String = 20, pageIndex As %String = 1) As %DynamicObject
{
set out = []
set vFrom = ((pageIndex -1 ) * pageSize)+1
set vTo = vFrom + (pageSize-1)
set sql = "SELECT ID,SSN,LastName,GivenName,SecondaryName, Gender, Age "_
"FROM Sample.Person WHERE Age > ? "_
"Order By "_sortField _" "_ $CASE(sortOrder,1:"ASC",2:"DESC",:"ASC")
Set rs=##class(%ScrollableResultSet).%New("%DynamicQuery:SQL")
set sc = rs.Prepare(sql)
set sc = rs.Execute(iAge) If $$$ISERR(sc) Do DisplayError^%apiOBJ(sc) Quit
if (rs.Count()=0) Quit {"msg" : "No Records Found"}
Do rs.CurrRowSet(vFrom)
if pageSize >= rs.Count() set pageSize = rs.Count()
try{
FOR i=1:1:pageSize{
Do out.%Push({
"index": (i),
"pid": (rs.%Get("ID")),
"ssn" : (rs.%Get("SSN")),
"lastname" : (rs.%Get("LastName")) ,
"givenname": (rs.%Get("GivenName")),
"secondaryname": (rs.%Get("SecondaryName")) ,
"gender": (rs.%Get("Gender")),
"age": (rs.%Get("Age") )
})
Do rs.%Next()
}
}
catch(e){ }
set outJson = []
Do outJson.%Push({
"pageSize":(pageSize),
"pageIndex":(pageIndex),
"fromIndex":(vFrom),
"toIndex":(vFrom+i - 1),
"resultSetTotal":(rs.Count()),
"pageRecords":(i),
"pages":($NORMALIZE((rs.Count()/pageSize),0)),
"resultSet":(out)
})
return outJson
}