Visualforce Pagination and Viewstate

Question

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.

Answer 1

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>

Answer 2

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.