5

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.

Improve this question
  • Its definitely a must have feature, was such a pain to implement a server side pagination with the beautiful datatable plugin. But ideally users don't have to see more than 2k records at a time, things work fine with pagination. Wondering if you have any specific requirement to allow user to view 20k records at a time? – Raul Mar 13 '17 at 8:39
  • @RahulSharma With the page I'm working on now, people want to be able to view a lot db rows all at once, and pagination with 2000 records per page seems reasonable. Although if they wanted more records per page, it wouldn't be too hard to pull down more queryMore results as needed to make the pages bigger. – martin Mar 13 '17 at 8:59
  • One idea would be to implement infinite scroll to load more data on scroll using remoting. – Raul Mar 13 '17 at 9:10
  • @RahulSharma What does your @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
  • 1
    @RahulSharma That's a nice idea, but it looks like currently the maximum OFFSET value in a SOQL query is only 2000. – martin Mar 13 '17 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.