2

I am working on a searchpage in Visualforce where pagination is needed on a very large dataset. StandardSetController does not seem to be a good fit due to the fact that the entire dataset is still saved in viewstate, no matter how many records are in the current page. SOQL Offset isn't a good fit because it only allows for offsets up to 2k records. I went with javascript remoting and it is choking at ~20k records (hitting the 15mb limit). Does anyone know of a good way to achieve pagination where only the current page of records is counting against the limits? Any help would be appreciated.

2
  • How many records are you expecting to have?
    – sfdcfox
    Aug 24, 2017 at 16:27
  • I've had good luck with angular loading upwards of 100k records.
    – Adrian Larson
    Aug 24, 2017 at 16:36

2 Answers 2

5

If you're going to use remoting, consider grabbing of the ID values, then grab the records you need when you need to load a page. All of this happens client-side. Here's what your controller might look like:

public class MyPageController {
    @RemoteAction @ReadOnly public static Id[] getRecordIds() {
        Id[] results = new Id[0];
        for(SObject record: [SELECT ... FROM ... WHERE ...]) {
            results.add(record.Id);
        }
        return results;
    }
    @RemoteAction public static SObject[] getRecords(Id[] recordIds) {
        return [SELECT ... FROM ... WHERE Id = :recordIds];
    }
}

This will allow pagination of up to ~750k records. If you want to be able to edit the current page of results, you could also direct the pagination via apex:actionFunction.

public class MyPageController {
    public Id[] pageRecordIds { get; set; }
    @RemoteAction @ReadOnly public static Id[] getRecordIds() {
        Id[] results = new Id[0];
        for(SObject record: [SELECT ... FROM ... WHERE ...]) {
            results.add(record.Id);
        }
        return results;
    }
    public SObject[] updatePage() {
        return [SELECT ... FROM ... WHERE Id = :pageRecordIds];
    }
}

Which you would call from a method defined from an apex:actionFunction:

<apex:actionFunction name="updatePageViewState" action="{!updatePage}" reRender="dataArea">
    <apex:param name="recordIds" assignTo="{!pageRecordIds}" value="" />
</apex:actionFunction>
2
  • sorry could you please help me clarifying what do you mean by this line: Id[] results = new Id[0]; or is this equivalent to List<Id> results = new List<Id>(); ? thanks
    – manza
    May 25, 2018 at 6:40
  • @manza Yes, they behave the same way, but one way takes less typing, so I prefer to use it in my everyday code.
    – sfdcfox
    May 25, 2018 at 10:28
0

Turns out, a good workaround is to use order by and limit together to create a custom offset. After the initial query, save the last record's value for the ordered field. Then if they request the next records, you just do your query where the results are greater than that value with the same limit. If they want previous, then do an order descending where the field is less than that value with the same limit. Very nice solution for the viewstate issue. It leaves some manual recreation for page numbers and total pages but ultimately allows for unlimited paging.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .