Pagination Leads To Repeat __KEY_root

[JBH_ABCis]

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.

[JBH_ABCis · Accepted solution]

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;