Workaround for Offset limitation

Question

I am currently working on setting up REST service in salesforce where in consumer can pass few parameters based on which there is logic to build and combine SOQL results and send in response

Sometimes request payload can fetch more than 50000 records so we decided to go with pagination combined with offset but encountered the error of maximum offset value 2000 so now trying to use the approach suggested by @sfdcfox here:

https://salesforce.stackexchange.com/questions/22631/workaround-for-offset-2000-limit-on-soql-query

I cannot pass more than 1000 records at once to the API consumer because the response can get heavy like more than 6MB when there are 1500+ish records. So maximum records we decided including safety net to keep it as 1000 per api response

Now the challenge is CreatedDate Vs Systemmodstamp

Lets say I have 50k records that needs to be sent in response. If I order them by CreatedDate ASC i can eventually send all the 50k records in 50 separate api calls made by consumer (1000 in each) as the createddate on records doesnt change and i can rely on this field.

Consumers are looking for recent updated records so i was thinking of using the systemmodtamp until i realized there will be scenarios where certain records can be left behind and not sent.

One such scenario is:

• There are 10 records (A, B, C, D, E, F, G, H, I, J) matching request payload and I lets assume I am sending 2 records per request ordered by systemmodstamp ASC
• First Iteration I have sent 2 records (A & B). Now lets say some sytem process updated record "B" it jumps to bottom of queue to be resent but when next time API invokes rest service to send next batch of 2 records, Ideally C & D should be sent but since B jumped to bottom, and C took spot #2 it will be skipped and D & E are sent.

Is there any alternative to overcome this and make sure all records are sent ?

   Note that there can be multiple consumers invoking this REST service so creating custom fields to track is not an option. Also this gets invoked hourly by different consumers

Answer 1

During pagination i try to do order by on 2 fields lets says first 1 will be created date, systemmodstamp or any other field you want to order by but second field I use is record id field.

So my first query will be

select id, <orderbyfield> from Account limit pageSize orderby orderbyfield, id 

Now for next page I will send 2 variables if my orderByfield is getting updated. first will be saved orderbyfield value... like in your case that will be systemmodstamp you received first time for b and second will be id of b.

So my query for next record will be like

select id, <orderbyfield> from Account where (orderbyfield = LastOrderField and id > lastid) or (orderbyfield > LastOrderField) limit pageSize orderby orderbyfield, id 

Even with his approach you will get some duplicate records where you are consuming this API those you can filter there by recordId key.