topic Re: Pagination Leads To Repeat __KEY_root in App Development

Pagination Leads To Repeat __KEY_root

JBH_ABCis — Thu, 09 Jan 2025 04:58:03 GMT

Hi everyone.

We're trying to make a REST-based app. The API requires pagination, because a single day's data can be thousands of records, and it only serves up to 500 records per page. While it does have a simple "page" query parameter, the output doesn't provide any "total pages" information. You have to keep paginating until it returns no new data.

Because of this, I can't use the built-in "Next page" pagination. I have set it to "Custom" and coded a loop which repeats until there are no new rows.

The problem is with the automatically-generated _KEY_root ID. Each loop starts again at 1. This causes mayhem with the automatic joins. See screenshot below, taken from a Table:

My script is below. This is a trimmed-down version that isn't trying to get all of the different tables.

LIB CONNECT TO 'Connections:Arbitrary REST'; let totalPages=4; //0 for unlimited pages let rows=0; let page=1; let pageSize=3; do RestTimesheets: SQL SELECT "GuidKey" AS "GuidKey_u12", "LastEdit" AS "LastEdit_u2", "DateStart", "PayDate", "PayStartTime", "PayEndTime", "ClockedStartTime", "ClockedEndTime", "GrossMinutes", "NetMinutes", "Breaks", "BreakMinutesUnpaid", "BreakMinutesPaid", "RosterGuidKey", "RosterStartTime", "RosterEndTime", "Area", "Event", "EventFunction", "Cost", "Authorised", "AuthorisedAt", "Paid" AS "Paid_u0", "Started", "Ended", "AdminLock", "Reversal", "Comments", "Deleted", "PayTypes", "__KEY_root", (SELECT "GuidKey", "Name", "__FK_LastEditBy" FROM "LastEditBy" FK "__FK_LastEditBy"), (SELECT "GuidKey" AS "GuidKey_u2", "LastEdit", "EmployeeCode", "Name" AS "Name_u2", "PayrollCode", "ExportPays", "__KEY_Employee", "__FK_Employee", (SELECT "GuidKey" AS "GuidKey_u0", "Name" AS "Name_u0", "ShortName", "ExportCode", "__FK_PayCompany" FROM "PayCompany" FK "__FK_PayCompany"), (SELECT "GuidKey" AS "GuidKey_u1", "Name" AS "Name_u1", "ShortName" AS "ShortName_u0", "ExportCode" AS "ExportCode_u0", "__FK_EmploymentType" FROM "EmploymentType" FK "__FK_EmploymentType") FROM "Employee" PK "__KEY_Employee" FK "__FK_Employee"), (SELECT "LastEdit" AS "LastEdit_u0", "LocationCode", "GuidKey" AS "GuidKey_u3", "Name" AS "Name_u3", "ShortName" AS "ShortName_u1", "ExportCode" AS "ExportCode_u1", "__FK_Location" FROM "Location" FK "__FK_Location"), (SELECT "GuidKey" AS "GuidKey_u4", "Name" AS "Name_u4", "ShortName" AS "ShortName_u2", "ExportCode" AS "ExportCode_u2", "__FK_Department" FROM "Department" FK "__FK_Department"), (SELECT "GuidKey" AS "GuidKey_u5", "Name" AS "Name_u5", "ShortName" AS "ShortName_u3", "ExportCode" AS "ExportCode_u3", "__FK_Role" FROM "Role" FK "__FK_Role"), (SELECT "GuidKey" AS "GuidKey_u6", "Name" AS "Name_u6", "ShortName" AS "ShortName_u4", "ExportCode" AS "ExportCode_u4", "__FK_Profile" FROM "Profile" FK "__FK_Profile"), (SELECT "GuidKey" AS "GuidKey_u7", "Name" AS "Name_u7", "__FK_Period" FROM "Period" FK "__FK_Period"), (SELECT "GuidKey" AS "GuidKey_u8", "Name" AS "Name_u8", "ShortName" AS "ShortName_u5", "ExportCode" AS "ExportCode_u5", "Unpaid", "IsLeaveType", "__FK_ShiftType" FROM "ShiftType" FK "__FK_ShiftType"), (SELECT "GuidKey" AS "GuidKey_u9", "Name" AS "Name_u9", "__FK_AuthorisedBy" FROM "AuthorisedBy" FK "__FK_AuthorisedBy"), (SELECT "GuidKey" AS "GuidKey_u11", "ActualStartTime", "ActualEndTime", "ActualMins", "AdjustedStartTime", "AdjustedEndTime", "AdjustedMins", "Paid", "Seq", "__KEY_Breaks", "__FK_Breaks", (SELECT "LastEdit" AS "LastEdit_u1", "LocationCode" AS "LocationCode_u0", "GuidKey" AS "GuidKey_u10", "Name" AS "Name_u10", "ShortName" AS "ShortName_u6", "ExportCode" AS "ExportCode_u6", "__FK_Location_u0" FROM "Location" FK "__FK_Location_u0") FROM "Breaks" PK "__KEY_Breaks" FK "__FK_Breaks") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION ( URL "$(base_address)/1.0/timesheets", HTTPHEADER "Authorization" "$(token)", QUERY "page" "$(page)", QUERY "pageSize" "$(pageSize)", QUERY "usePayDate" "false", QUERY "dateMin" "$(start_date)", QUERY "dateMax" "$(end_date)" ); let newRows = NoOfRows('RestTimesheets'); exit do unless (newRows > rows) and (totalPages=0 or page<totalPages); //If we haven't gained new rows from this loop, end it rows = newRows; page = page+1; loop; [Timesheets]: LOAD [GuidKey_u12] AS [TimesheetUUID], [LastEdit_u2] AS [TimesheetLastEdit], [DateStart] AS [TimesheetDateStart], [PayDate] AS [TimesheetPayDate], [PayStartTime] AS [TimesheetPayStartTime], [PayEndTime] AS [TimesheetPayEndTime], [ClockedStartTime] AS [TimesheetClockedStartTime], [ClockedEndTime] AS [TimesheetClockedEndTime], [GrossMinutes] AS [TimesheetGrossMinutes], [NetMinutes] AS [TimesheetNetMinutes], [Breaks] AS [TimesheetBreaks], [BreakMinutesUnpaid] AS [TimesheetBreakMinutesUnpaid], [BreakMinutesPaid] AS [TimesheetBreakMinutesPaid], [RosterGuidKey] AS [TimesheetRosterUUID], [RosterStartTime] AS [TimesheetRosterStartTime], [RosterEndTime] AS [TimesheetRosterEndTime], [Area] AS [TimesheetArea], [Event] AS [TimesheetEvent], [EventFunction] AS [TimesheetEventFunction], [Cost] AS [TimesheetCost], [Authorised] AS [TimesheetAuthorised], [AuthorisedAt] AS [TimesheetAuthorisedAt], [Paid_u0] AS [TimesheetPaid], [Started] AS [TimesheetStarted], [Ended] AS [TimesheetEnded], [AdminLock] AS [TimesheetAdminLock], [Reversal] AS [TimesheetReversal], [Comments] AS [TimesheetComments], [Deleted] AS [TimesheetDeleted], [PayTypes] AS [TimesheetPayTypes] ,[__KEY_root] RESIDENT RestTimesheets WHERE NOT IsNull([__KEY_root]) ; [Location]: LOAD DISTINCT [LastEdit_u0] AS [TimesheetLocationLastEdit], [LocationCode] AS [TimesheetLocationLocationCode], [GuidKey_u3] AS [TimesheetLocationUUID], [Name_u3] AS [TimesheetLocationName], [ShortName_u1] AS [TimesheetLocationShortName] ,[__FK_Location] AS [__KEY_root] RESIDENT RestTimesheets WHERE NOT IsNull([__FK_Location]); [Department]: LOAD DISTINCT [GuidKey_u4] AS [TimesheetDepartmentUUID], [Name_u4] AS [TimesheetDepartmentName], [ShortName_u2] AS [TimesheetDepartmentShortName], [ExportCode_u2] AS [TimesheetDepartmentExportCode], [__FK_Department] AS [__KEY_root] RESIDENT RestTimesheets WHERE NOT IsNull([__FK_Department]); DROP TABLE RestTimesheets;

Is there any way I could either a) have the __KEY_root recognise the pagination, and start each loop at the level it should be or b) use a different primary key, derived from the data? Each object has a unique UUID, so if the root "Timesheets" table could get the UUIDs from Location and Department, it could join on those rather than needing any automatic keys to begin with.

Re: Pagination Leads To Repeat __KEY_root

JBH_ABCis — Thu, 09 Jan 2025 05:32:38 GMT

I figured this out.

You cannot change how __KEY_root is generated on the level of SQL SELECT. However, you can change how it's loaded into each table. So I brought the tables into the loop: on each iteration, each table loads the items from the current page, and adds (pageNumber-1)*pageSize to the KEY, so that it has a running total. To avoid repeats, I also drop the table on each loop, which is unfortunate.

New code is below:

let page=1; let pageSize=500; let rows=0; DO LIB CONNECT TO 'Connections:Arbitrary REST'; RestTimesheets: SQL SELECT "GuidKey" AS "GuidKey_u12", "LastEdit" AS "LastEdit_u2", "DateStart", "PayDate", "PayStartTime", "PayEndTime", "ClockedStartTime", "ClockedEndTime", "GrossMinutes", "NetMinutes", "Breaks", "BreakMinutesUnpaid", "BreakMinutesPaid", "RosterGuidKey", "RosterStartTime", "RosterEndTime", "Area", "Event", "EventFunction", "Cost", "Authorised", "AuthorisedAt", "Paid" AS "Paid_u0", "Started", "Ended", "AdminLock", "Reversal", "Comments", "Deleted", "PayTypes", "__KEY_root", (SELECT "GuidKey", "Name", "__FK_LastEditBy" FROM "LastEditBy" FK "__FK_LastEditBy"), (SELECT "GuidKey" AS "GuidKey_u2", "LastEdit", "EmployeeCode", "Name" AS "Name_u2", "PayrollCode", "ExportPays", "__KEY_Employee", "__FK_Employee", (SELECT "GuidKey" AS "GuidKey_u0", "Name" AS "Name_u0", "ShortName", "ExportCode", "__FK_PayCompany" FROM "PayCompany" FK "__FK_PayCompany"), (SELECT "GuidKey" AS "GuidKey_u1", "Name" AS "Name_u1", "ShortName" AS "ShortName_u0", "ExportCode" AS "ExportCode_u0", "__FK_EmploymentType" FROM "EmploymentType" FK "__FK_EmploymentType") FROM "Employee" PK "__KEY_Employee" FK "__FK_Employee"), (SELECT "LastEdit" AS "LastEdit_u0", "LocationCode", "GuidKey" AS "GuidKey_u3", "Name" AS "Name_u3", "ShortName" AS "ShortName_u1", "ExportCode" AS "ExportCode_u1", "__FK_Location" FROM "Location" FK "__FK_Location"), (SELECT "GuidKey" AS "GuidKey_u4", "Name" AS "Name_u4", "ShortName" AS "ShortName_u2", "ExportCode" AS "ExportCode_u2", "__FK_Department" FROM "Department" FK "__FK_Department"), (SELECT "GuidKey" AS "GuidKey_u5", "Name" AS "Name_u5", "ShortName" AS "ShortName_u3", "ExportCode" AS "ExportCode_u3", "__FK_Role" FROM "Role" FK "__FK_Role"), (SELECT "GuidKey" AS "GuidKey_u6", "Name" AS "Name_u6", "ShortName" AS "ShortName_u4", "ExportCode" AS "ExportCode_u4", "__FK_Profile" FROM "Profile" FK "__FK_Profile"), (SELECT "GuidKey" AS "GuidKey_u7", "Name" AS "Name_u7", "__FK_Period" FROM "Period" FK "__FK_Period"), (SELECT "GuidKey" AS "GuidKey_u8", "Name" AS "Name_u8", "ShortName" AS "ShortName_u5", "ExportCode" AS "ExportCode_u5", "Unpaid", "IsLeaveType", "__FK_ShiftType" FROM "ShiftType" FK "__FK_ShiftType"), (SELECT "GuidKey" AS "GuidKey_u9", "Name" AS "Name_u9", "__FK_AuthorisedBy" FROM "AuthorisedBy" FK "__FK_AuthorisedBy"), (SELECT "GuidKey" AS "GuidKey_u11", "ActualStartTime", "ActualEndTime", "ActualMins", "AdjustedStartTime", "AdjustedEndTime", "AdjustedMins", "Paid", "Seq", "__KEY_Breaks", "__FK_Breaks", (SELECT "LastEdit" AS "LastEdit_u1", "LocationCode" AS "LocationCode_u0", "GuidKey" AS "GuidKey_u10", "Name" AS "Name_u10", "ShortName" AS "ShortName_u6", "ExportCode" AS "ExportCode_u6", "__FK_Location_u0" FROM "Location" FK "__FK_Location_u0") FROM "Breaks" PK "__KEY_Breaks" FK "__FK_Breaks") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION ( URL "$(base_address)/1.0/timesheets", HTTPHEADER "Authorization" "$(token)", QUERY "page" "$(page)", QUERY "pageSize" "$(pageSize)", QUERY "usePayDate" "false", QUERY "dateMin" "$(start_date)", QUERY "dateMax" "$(end_date)" ); [Location]: LOAD DISTINCT [LastEdit_u0] AS [TimesheetLocationLastEdit], [LocationCode] AS [TimesheetLocationLocationCode], [GuidKey_u3] AS [TimesheetLocationUUID], [Name_u3] AS [TimesheetLocationName], [ShortName_u1] AS [TimesheetLocationShortName] ,($(page)-1)*$(pageSize)+[__FK_Location] AS [__KEY_timesheet] RESIDENT RestTimesheets WHERE NOT IsNull([__FK_Location]); [Timesheets]: LOAD [GuidKey_u12] AS [TimesheetUUID], [LastEdit_u2] AS [TimesheetLastEdit], [DateStart] AS [TimesheetDateStart], [PayDate] AS [TimesheetPayDate], [PayStartTime] AS [TimesheetPayStartTime], [PayEndTime] AS [TimesheetPayEndTime], [ClockedStartTime] AS [TimesheetClockedStartTime], [ClockedEndTime] AS [TimesheetClockedEndTime], [GrossMinutes] AS [TimesheetGrossMinutes], [NetMinutes] AS [TimesheetNetMinutes], [Breaks] AS [TimesheetBreaks], [BreakMinutesUnpaid] AS [TimesheetBreakMinutesUnpaid], [BreakMinutesPaid] AS [TimesheetBreakMinutesPaid], [RosterGuidKey] AS [TimesheetRosterUUID], [RosterStartTime] AS [TimesheetRosterStartTime], [RosterEndTime] AS [TimesheetRosterEndTime], [Area] AS [TimesheetArea], [Event] AS [TimesheetEvent], [EventFunction] AS [TimesheetEventFunction], [Cost] AS [TimesheetCost], [Authorised] AS [TimesheetAuthorised], [AuthorisedAt] AS [TimesheetAuthorisedAt], [Paid_u0] AS [TimesheetPaid], [Started] AS [TimesheetStarted], [Ended] AS [TimesheetEnded], [AdminLock] AS [TimesheetAdminLock], [Reversal] AS [TimesheetReversal], [Comments] AS [TimesheetComments], [Deleted] AS [TimesheetDeleted], [PayTypes] AS [TimesheetPayTypes] ,($(page)-1)*$(pageSize)+[__KEY_root] AS [__KEY_timesheet] RESIDENT RestTimesheets WHERE NOT IsNull([__KEY_root]) ; [Department]: LOAD [GuidKey_u4] AS [TimesheetDepartmentUUID], [Name_u4] AS [TimesheetDepartmentName], [ShortName_u2] AS [TimesheetDepartmentShortName], [ExportCode_u2] AS [TimesheetDepartmentExportCode], ($(page)-1)*$(pageSize)+[__FK_Department] AS [__KEY_timesheet] RESIDENT RestTimesheets WHERE NOT IsNull([__FK_Department]); let newRows = NoOfRows('RestTimesheets'); DROP TABLE RestTimesheets; //Drop the table each loop so we're only adding THIS page exit do unless newRows > 0; //If we haven't gained new rows from this loop, end it page = page+1; loop;