When you run a query through the REST api and the number of records returned exceeds the default 2000, or the batch size set in the Sforce-Query-Options
header, the query will return a nextRecordsUrl
with an 01gXXXXXXXXXXXXXXX
id that can be used to retrieve records at arbitrary points.
For example, if you have a page that shows all accounts, you could start off by performing the query:
$curl https://ap4.salesforce.com/services/data/v38.0/query?q=SELECT+name+from+Account
And then you could have a list of page numbers, and if the user clicked on page 10 for example, you could get the data for that page with this query:
$curl https://ap4.salesforce.com/services/data/v38.0/query/01g6F00006jeq68QAA-20000
(Headers not shown for brevity)
The SOAP api (and the related Ajax toolkit) has a queryMore
function that also works similarly.
This all works great when working with the api's, but is there an Apex equivalent of this feature?
The SOQL OFFSET
keyword initially looked promising, but it is limited in the number of records it can operate over:
list<Account> alist = [SELECT Name FROM Account LIMIT 2000 OFFSET 20000];
System.QueryException: Maximum SOQL offset allowed is 2000
The Standard Set controller also looked promising with the setPageNumber()
and getRecords()
methods, but it is also similarly limited. (Presumably because it was designed to be used in VF pages and sent in its entirety in the view state).
Note: The maximum record limit for StandardSetController is 10,000 records. Instantiating StandardSetController using a query locator returning more than 10,000 records causes a LimitException to be thrown. However, instantiating StandardSetController with a list of more than 10,000 records doesn’t throw an exception, and instead truncates the records to the limit.
Ultimately, I'm working on a page that should give users the ability to view all/any of over 20,000 records on an object. I was thinking about using Javascript remoting, and it wouldn't really be a problem to use the AJAX toolkit instead, but I was a bit surprised I haven't been able to track down the corresponding feature in Apex.
There seems to have been a similar discussion on this site a few years ago here:
How to query more than 50000 records in apex and bind it in vf page in jqgrid and paging?
I'm not sure how the top-voted answer there was to use the StandardSetController - especially when the OP commented that it doesn't work.
@RemoteAction
method look like in the controller then? How do you manage the query so that subsequent calls for more data pick up where the last one left off? – martin Mar 13 '17 at 9:14