How to paginate > 10000 records. SOQL OFFSET and StandardSetController too limiting

Question

I am working on pagination and i am able to display my records on my VF page and i have done pagination to get the record get display. But the pagination work only when the no of records is less than 2000. If the number of records are more than 2000 and when we click on the last button which show all the last records salesforce throws an error

Maximum SOQL offset allowed is 2000 Error is in expression '{!endbtn}' in component <apex:commandButton> in page accountpagination_vf. An unexpected error has occurred. Your development organization has been notified.

I understand why this issue is getting its because salesforce have limit of 2000 on records for pagination. But mu client have more than 2000 records and he want all to be get displayed on the VF page on pagination. Please guide me the best way to achieve this scenario. my code for VF page :

<apex:page controller="accountPagination_ctrl">
 <apex:pageBlock >
  <apex:form >
      <apex:commandButton value="Search" action="{!searchresult}"/>

  <apex:dataTable value="{!accLst}" var="ac">
      <apex:column headerValue="Name">
          <apex:outputField value="{!ac.Name}"/>
      </apex:column>
      <apex:column headerValue="Id">
          <apex:outputField value="{!ac.Id}"/>
      </apex:column>
  </apex:dataTable>

  <apex:commandButton value="FIRST" action="{!fstbtn}" disabled="{!prv}"/>
  <apex:commandButton value="PRE" action="{!prvbtn}" disabled="{!prv}"/>
  <apex:commandButton value="NXT" action="{!nextbtn}" disabled="{!nxt}"/>
  <apex:commandButton value="END" action="{!endbtn}" disabled="{!nxt}"/>
 </apex:form>
</apex:pageBlock>

controller :

public class accountPagination_ctrl{

public List<Contact> accLst {get; set;}
public List<Contact> accLst_size {get; set;}
public integer counter = 0;
public integer recNum = 0;
public integer limits = 5;
public set<id> contactID {get; set;}

public accountPagination_ctrl(){
    accLst = new List<Contact> ();
    accLst_size = new List<Contact> ();
    contactID = new set<id>();
    Result();
}

public void Result(){
    accLst_size = [SELECT Name, Id FROM Contact WHERE name != null];
    recNum = accLst_size.size();

}
public void searchresult(){


    //getResult();
    SYSTEM.DEBUG('Number of records ' + recNum ); 
    accLst = [SELECT Name, Id FROM Contact WHERE id in: contactID and name != null LIMIT :limits OFFSET: counter  ];

}

public void nextbtn(){
    counter += limits ;
    searchresult();
}

public void prvbtn(){
    counter -= limits ;
    searchresult();
}

public void fstbtn(){
    counter = 0;
    searchresult();
}

public void endbtn(){
    counter = recNum - math.mod(recNum,limits);
    searchresult();
}

public boolean getprv(){
    if(counter == 0)
        return true;
    else
        return false;
}

public boolean getnxt(){
    if((counter + limits) > recNum)
        return true;
    else
        return false;
}
}

please help me out i am open to any idea you suggest.

Answer 1

There is a good article about pagination in Apex here: https://developer.salesforce.com/page/Paginating_Data_for_Force.com_Applications that lists various options.

You have two options for pagination inside of Visualforce pages, OFFSET pagination, and StandardSetController pagination. With OFFSET pagination you are limited to 2,000 records; however, with a StandardSetController you can paginate up to 10,000. So, one thing you could do would be to refactor to use the StandardSetController pagination.

Another thing that you could do, would be to add some sort of filtering capability, so that the user can get to the records they need. For example, if you would return more than 2,000 records, display a message saying something like "Showing 2,000 records but there could be more. Please use a filter to narrow down the results."

Answer 2

I would look into using StandardSetController, see the docs here. This does not make it unlimited however, but it ups the limit from 2,000 to 10,000, which might help. In general, I try to make filters on the page to allow the end user to filter down the size of returned results.

Here are a few blog posts about using StandardSetController for pagination. Hope that helps.

http://forceguru.blogspot.com/2011/04/pagination-in-salesforce.html

http://hisrinu.wordpress.com/2012/01/09/pagination-using-standardsetcontroller/