How can I use 'query more' using javascript remoting?

Question

I have an object with 50000 records in it, which I need to retrieve in chunks of 100 records per call/chunk.

I tried using OFFSET, but this way you can not call more than 2000 records.

Is there an example of how to use javascript remoting to do this? or maybe a different approach?

Answer 1

You can just be sneaky about it and have the system use "offset" for you:

@RemoteAction public static SObject[] getRecords(Id offsetId) {
    if(offsetId==null) {
        return [SELECT Id,Name FROM Contact ORDER BY Id ASC LIMIT 100];
    } else {
        return [SELECT Id,Name FROM Contact WHERE ID > :offsetId ORDER BY Id ASC LIMIT 100];
    }
}

From here, you can then just query away happily:

function handleResult(result, event) {
    // handle a batch here
    // do more
    if(result.length===100)
        {!$RemoteAction.controller.getRecords}(result[99].Id,handleResult);
    }
}
{!$RemoteAction.controller.getRecords}(null,handleResult);

This works even if you combine it with other criteria; you don't have to filter on Id alone. We're just using the ID to leverage the index and create our own pseudo-offset.

Answer 2

There is an alternative which is kind of nasty but one way of managing OFFSET Limits.

1) create a autonumber field in the object you use and convert the string to a number and tada you have auto numbers as unique record identifiers for every records(there can be no duplicate, which ensures every record gets a unique number/offset) Downside of this is that its a sequential number and if one of the records get deleted and say you use select id from account order by offset__C asc

there are chances that the list may return only 2800 where 200 records were deleted

Answer 3

Im afraid I dont think its possible to get the 50,000 rows via remoting. Checking https://login.salesforce.com/help/doc/en/salesforce_app_limits_cheatsheet.pdf (page 39 & 40) shows the maximum OFFSET you can use is 2000 and the maximum rows per query is 200.

I have seen other similar questions and the recommended course of action is to export the data from salesforce with the Data Loader (Setup -> Admin -> Data Management -> Data Loader). I havent tried this myself and i'm unsure of what your trying to achieve but hopefully this helps. https://eu1.salesforce.com/dwnld/DataLoader/ApexDataLoader.exe

Answer 4

If you annotate your @RemoteAction with @ReadOnly you can query up to 1MIL rows.

In order to page through this result set to extract a subset you will need to make sure your order by ends with ID ASC so you guarantee the records are always coming back in the same order.

You will also need to keep track of the last id seen on the previous page so you can use it as a key when you're looping over your set.

Pseudo code

// I'm on page 20 and the user clicks 'Next' 
// Grab the id of the last record displayed
Id lastRecordSeen = Param from JS Remoting 
List<SObjects> records = [Select Id, Name, etc from Sobject Order by Name ASC, ID ASC]; //ID Must be last in order by

List<Sobject> nextPageOfRecords = new List<Sobject>();
Integer lastRecordSeenPosition;
//Loop over all records returned by SOQL looking for our needle
for(Integer i = 0; i< records.size(); i++){
   if(records[i].Id == lastRecordSeen){
   //We matched our needle now lets save the index of the first record that will be on the 'next' page
      lastRecordSeenPosition = i+1;
      break;
   }   
}

//Add our subset of records to a new list
for(Integer i = lastRecordSeenPosition; i < lastRecordSeenPosition + recordsPerPage; i++){
   nextPageOfRecords.add(records[i];
}

You'll need to do something similar for moving backwards and of course you'll want to just use OFFSET and LIMIT if the position is less than 2k.