SOQL pagination OFFSET 2000 limit ordered by Created Date for bulk imported records

Question

I have a requirement to present records with infinite scroll (e.g. using lightning-datatable, but frontend presentation part of this is irrelevant) ordered by created date descending (most recent records first).

If i use OFFSET operator in my SOQL, i run into 2000 offset limit.

If i use the workaround (which afaic is written by @sfdcfox) - https://help.salesforce.com/articleView?id=000339353&type=1&mode=1 and i have to paginate records which were bulk imported (e.g. from CSV) then i can skip thousands of rows because they all end up having the same CreatedDate.

E.g. consider this:

|--Id--|-----Date-----|
|20003-|--2019-01-02--|
|20002-|--2019-01-01--| <- Suppose my first page ended here
|20001-|--2019-01-01--|

Now if i run SELECT Id FROM My_Object__c WHERE CreatedDate > 2019-01-01 ORDER BY CreatedDate LIMIT 2000 i will get nothing back, because records 2 and 3 share the same created date (to millisecond!) coz they came from Bulk import.

This answer here: RE: Workaround for Offset 2000 limit on SOQL Query just says 'use Id instead'. This helped me to solve some requirements, but in a specific business case my stakeholders want to achieve both infinite scroll and ORDER BY created date.

UPDATE While i like the AutoNumber field idea, in part of my requirements is building this UI for ContentDocument, and AFAIC i can't add fields to that?

Answer 1

We came across the similar issue previously.

First, the granularity of createdDate is precised to seconds instead of days. So if you don't have too many records created at the same second, you are probably fine. What you can do is instead of using CreatedDate > lastQueriedCreatedDate, use >=. You could have a few overlapping, but that's still better than missing records. And you can handle that logic in your controller.

If the above assumption is not the case, e.g., in the bulk scenario you created thousands of records at the same second. What you can do is to create a new pseudo auto-number field which is strictly increasing by createdDate. You can use that field to do pagination instead of the original createdDate field. The handling of existing data is a bit of a pain but still resolvable.