Pagination using standardsetcontroller

As the number of records increases, the time required for the browser to render them increases. Paging is used to reduce the amount of data exchanged with the client. Paging is typically handled on the server side (standardsetcontroller). The page sends parameters to the controller, which the controller needs to interpret and then respond with the appropriate data set.

 

Here is the controller which makes use of standard set controller for pagination

public with sharing class Pagination {
    Public Integer noOfRecords{get; set;}
    Public Integer size{get;set;}
    public ApexPages.StandardSetController setCon {
        get{
            if(setCon == null){
                size = 10;
                string queryString = 'Select Name, Type, BillingCity, BillingState, BillingCountry from Account order by Name';
                setCon = new ApexPages.StandardSetController(Database.getQueryLocator(queryString));
                setCon.setPageSize(size);
                noOfRecords = setCon.getResultSize();
            }
            return setCon;
        }set;
    }
    
    Public List<Account> getAccounts(){
        List<Account> accList = new List<Account>();
        for(Account a : (List<Account>)setCon.getRecords())
            accList.add(a);
        return accList;
    }
    
    public pageReference refresh() {
        setCon = null;
        getAccounts();
        setCon.setPageNumber(1);
        return null;
    }
    
    public Boolean hasNext {
        get {
            return setCon.getHasNext();
        }
        set;
    }
    public Boolean hasPrevious {
        get {
            return setCon.getHasPrevious();
        }
        set;
    }
 
    public Integer pageNumber {
        get {
            return setCon.getPageNumber();
        }
        set;
    }
 
    public void first() {
        setCon.first();
    }
 
    public void last() {
        setCon.last();
    }
 
    public void previous() {
        setCon.previous();
    }
 
    public void next() {
        setCon.next();
    }
}

Using the above controller methods we can define the pagination.

<apex:page controller="Pagination">
    <apex:form >
        <apex:pageBlock id="pb">
            <apex:pageBlockTable value="{!Accounts}" var="a">
                <apex:column value="{!a.Name}"/>
                <apex:column value="{!a.Type}"/>
                <apex:column value="{!a.BillingCity}"/>
                <apex:column value="{!a.BillingState}"/>
                <apex:column value="{!a.BillingCountry}"/>
            </apex:pageBlockTable>
            <apex:panelGrid columns="7">
                <apex:commandButton status="fetchStatus" reRender="pb" value="|<" action="{!first}" disabled="{!!hasPrevious}" title="First Page"/>
                <apex:commandButton status="fetchStatus" reRender="pb" value="<" action="{!previous}" disabled="{!!hasPrevious}" title="Previous Page"/>
                <apex:commandButton status="fetchStatus" reRender="pb" value=">" action="{!next}" disabled="{!!hasNext}" title="Next Page"/>
                <apex:commandButton status="fetchStatus" reRender="pb" value=">|" action="{!last}" disabled="{!!hasNext}" title="Last Page"/>
                <apex:outputText >{!(pageNumber * size)+1-size}-{!IF((pageNumber * size)>noOfRecords, noOfRecords,(pageNumber * size))} of {!noOfRecords}</apex:outputText>
                <apex:commandButton status="fetchStatus" reRender="pb" value="Refresh" action="{!refresh}" title="Refresh Page"/>
                <apex:outputPanel style="color:#4AA02C;font-weight:bold">
                    <apex:actionStatus id="fetchStatus" startText="Fetching..." stopText=""/>
                </apex:outputPanel>
            </apex:panelGrid>
        </apex:pageBlock>
    </apex:form>
</apex:page>

Do you really feel the code is little larger then what you expect, here is the minified version of controller and page
Controller Code:

public with sharing class Pagination_min {
    Public Integer noOfRecords{get; set;}
    Public Integer size{get;set;}
    public ApexPages.StandardSetController setCon {
        get{
            if(setCon == null){
                size = 10;
                string queryString = 'Select Name, Type, BillingCity, BillingState, BillingCountry from Account order by Name';
                setCon = new ApexPages.StandardSetController(Database.getQueryLocator(queryString));
                setCon.setPageSize(size);
                noOfRecords = setCon.getResultSize();
            }
            return setCon;
        }set;
    }
    
    Public List<Account> getAccounts(){
        List<Account> accList = new List<Account>();
        for(Account a : (List<Account>)setCon.getRecords())
            accList.add(a);
        return accList;
    }
    
    public pageReference refresh() {
        setCon = null;
        getAccounts();
        setCon.setPageNumber(1);
        return null;
    }
}

Page Code:

<apex:page controller="Pagination_min">
    <apex:form >
        <apex:pageBlock id="pb">
            <apex:pageBlockTable value="{!Accounts}" var="a">
                <apex:column value="{!a.Name}"/>
                <apex:column value="{!a.Type}"/>
                <apex:column value="{!a.BillingCity}"/>
                <apex:column value="{!a.BillingState}"/>
                <apex:column value="{!a.BillingCountry}"/>
            </apex:pageBlockTable>
            <apex:panelGrid columns="7">
                <apex:commandButton status="fetchStatus" reRender="pb" value="|<" action="{!setCon.first}" disabled="{!!setCon.hasPrevious}" title="First Page"/>
                <apex:commandButton status="fetchStatus" reRender="pb" value="<" action="{!setCon.previous}" disabled="{!!setCon.hasPrevious}" title="Previous Page"/>
                <apex:commandButton status="fetchStatus" reRender="pb" value=">" action="{!setCon.next}" disabled="{!!setCon.hasNext}" title="Next Page"/>
                <apex:commandButton status="fetchStatus" reRender="pb" value=">|" action="{!setCon.last}" disabled="{!!setCon.hasNext}" title="Last Page"/>
                <apex:outputText >{!(setCon.pageNumber * size)+1-size}-{!IF((setCon.pageNumber * size)>noOfRecords, noOfRecords,(setCon.pageNumber * size))} of {!noOfRecords}</apex:outputText>
                <apex:commandButton status="fetchStatus" reRender="pb" value="Refresh" action="{!refresh}" title="Refresh Page"/>
                <apex:outputPanel style="color:#4AA02C;font-weight:bold">
                    <apex:actionStatus id="fetchStatus" startText="Fetching..." stopText=""/>
                </apex:outputPanel>
            </apex:panelGrid>
        </apex:pageBlock>
    </apex:form>
</apex:page>
About these ads
This entry was posted in Apex, salesforce, Salesforce Tips & Tricks, Uncategorized, Visualforce and tagged . Bookmark the permalink.

34 Responses to Pagination using standardsetcontroller

  1. Satarupa Roy says:

    Awesome!!

  2. calvinrn says:

    Hey, how would this work with a dynamic search page…I have a visual force search page and I have used the stadardsetcontroller for pagination. The initial query during page load is working for me. Subsequent queries are not executed. How do we rerun the query in the controller. I thought we could just use the getrecords() method after setting the controller object to null.

  3. Srini says:

    You need to assign the search/query results to standard set controller, then only it works otherwise it will not be able to retrieve them

    • calvinrn says:

      Thanks for the reply Srini. You are correct. But I am not sure how to reassign values to the standard set controller. For example, during page load I instatiate it with a default query. Next if the user enters some date values, I build a query with those dates and need it to be executed by the controller. How do I instantiate it again?

  4. Srini says:

    write a separate method for search and in this you need to assign the query results
    setCon = new ApexPages.StandardSetController(Database.getQueryLocator(List));

    Hope this helps

  5. calvinrn says:

    Yup this is now solved. I was also making a few other errors. Thanks a lot.

  6. Sumanth says:

    Srini Help in building pagination where I also need apply sorting of data based on Columns
    As we do on standard records, click on Record ID data gets sort based on that column.

    So I want combination of pagination + Sorting , Is this possible using StandardSet Controller ?

  7. Srini says:

    Sumanth, there is no out of the box functionality to do the sorting until and unless you reinitialize your standardsetcontroller using order by asc or desc. Hope this helps :)

  8. Sumanth says:

    Can we include this pagination as inline VF ?

    • Srini says:

      Yes… you should be able to do it

      • Sumanth says:

        What I did is , modified your code instead of Accounts had Cases in one page1
        now in one more page2 included this page1

        page2 is standardcontroller so that i can include this as inline vf

        Now im able to see only few records when i include as inline, but actual i can see more records

      • Srini says:

        Sumanth… until and unless I see the code, I can’t help you out ;)

  9. Srip says:

    Can this be used only on standard objects?

  10. Srip says:

    Can this StandardSetController be used only on standard objects?

  11. Mike Griggs says:

    THank you very much for this. Just what I was looking for! Now, I’m going to attempt to merge this with the Exj example and we should have a great looking table.

  12. Oliver says:

    Seems like when I use the code, of course with some modifications, what would the problem be if it displays ALL the results on one page. Although it says, for example, showing 1-10 of 39.

  13. Sumanth says:

    @Srini — How do we refresh this VF page automatically , I have tried action poller, its just incrementing count but not refreshing data in page block table.

    If I use this , I can`t reRender pageblock , how to achieve this ?

    • Sumanth says:

      Page which has the same set Controller

    • Srini says:

      Using the action poller you can refresh the page, however in order to execute the next and previous there should be some action on the buttons or else you need to call these methods in the action poller function

      • Sumanth says:

        Used action poller , but no luck , pageblock table data is not refreshed for some reason.
        If polling time is 30 seconds, in that time span if i create a record , that detail is not reflected over here,

  14. Calvin says:

    I am having some issues here. The next, previous etc methods do not work unless I make a call to the method which fetches records.
    Also the next, previous etc links start freezing up after I click on them continuously for some time.
    What am I doing wrong?

    • Srini says:

      Without looking at your code, it is difficult to tell you what is wrong. Post your code here

      • calvinrn says:

        Hi Srini,

        Here is my controller code. Thanks. As you can see in the next(),previous() etc I need to make a call to the method which in turn calls con.getRecords(). Please let me know what I am doing wrong.
        Also I am not able to figure out why the next,previous etc links are freezing up if I keep clicking them. Is the list in the back end getting filled up or something. If so how can I avoid the freezing?

        Thanks a lot for your help.

        public with sharing class FundingReportController {

        // the soql without the order and limit
        private String soql {get;set;}

        Public Integer size{get;set;}
        Public Integer noOfRecords{get; set;}

        //export to excel – returns a page reference to the AccountDataExcel page
        public PageReference exportToExcel() {
        return Page.fundingreportExcel;
        }

        // the collection of money transactions to display
        //public List moneyTransactions{get;set;}
        public List moneyTransactions{get;set;}

        // instantiate the StandardSetController from a query locator
        public ApexPages.StandardSetController con {
        get {
        if(con == null) {
        size=10;
        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, ‘Inside StandardSetController ‘+soql + ‘ order by ‘ + sortField + ‘ ‘ + sortDir));
        con = new ApexPages.StandardSetController(Database.getQueryLocator(soql + ‘ order by ‘ + sortField + ‘ ‘ + sortDir + ‘ limit 100′));
        // sets the number of records in each page set
        con.setPageSize(size);
        noOfRecords = con.getResultSize();
        }
        return con;
        }
        set;
        }

        // returns a list of wrapper objects for the sObjects in the current page set
        public List getMoneyTransactions() {
        try{
        //moneyTransactions = new List();
        moneyTransactions = new List();
        for (Money_Transaction__c mt: (List)con.getRecords())
        {
        moneyTransactions.add(mt);
        }

        }
        catch (Exception e)
        {
        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, ‘Code Error ‘+e));
        }
        return moneyTransactions ;
        }

        // the current sort direction. defaults to asc
        public String sortDir {
        get { if (sortDir == null) { sortDir = ‘asc'; } return sortDir; }
        set;
        }

        // the current field to sort by. defaults to last name
        public String sortField {
        get { if (sortField == null) {sortField = ‘Settlement_Date_First__c'; } return sortField; }
        set;
        }

        // format the soql for display on the visualforce page
        public String debugSoql {
        //get { return soql + ‘ order by ‘ + sortField + ‘ ‘ + sortDir + ‘ limit 20′; }
        get { return soql + ‘ ‘ + sortDir; }
        set;
        }

        // init the controller and display some sample data when the page loads
        public FundingReportController() {
        //Default dates: 6 months before today
        Date fromDate = date.today();
        fromDate = fromDate.addMonths(-6);
        String fromDateStr = String.ValueOf(fromDate);
        //ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR,fromDateStr ));
        //Default dates: Today
        Date toDate = date.today();
        String toDateStr = String.ValueOf(toDate);

        //soql = ‘select ACH_Type__c, Settlement_Date_First__c, Total_ACH_Amount__c from Money_Transaction__c';
        //soql = ‘select ACH_Type__c, Settlement_Date_First__c,MAX(Total_ACH_Amount__c) from Money_Transaction__c where Settlement_Date_First__c = 2012-01-01 group by ACH_Type__c,Settlement_Date_First__c ORDER BY MAX(Total_ACH_Amount__c)';
        //soql = ‘select ACH_Type__c, Settlement_Date_First__c,Total_ACH_Amount__c,Settlement__r.id,Settlement__r.name,Money_Movement_Type__c,Bank_Name__c,Bank_Account_Number__c,Tax_Batch__c from Money_Transaction__c where Settlement_Date_First__c = 2012-01-01′ + ‘ AND ACH_Type__c != \’VHR DDP Disbursement\”;
        soql = ‘select ACH_Type__c, Settlement_Date_First__c, Total_ACH_Amount__c,Settlement__r.id,Settlement__r.name,Money_Movement_Type__c,Bank_Name__c,Bank_Account_Number__c,Tax_Batch__c,Payroll_Group_Detail__c from Money_Transaction__c where Settlement_Date_First__c = ‘ + fromDateStr + ‘ AND ACH_Type__c != \’VHR DDP Disbursement\”;
        //soql = ‘select ACH_Type__c, Settlement_Date_First__c,Total_ACH_Amount__c,Settlement__r.id,Settlement__r.name,Money_Movement_Type__c,Bank_Name__c,Bank_Account_Number__c,Tax_Batch__c from Money_Transaction__c where Settlement_Date_First__c = 2012-01-01′ + ‘ AND ACH_Type__c != \’VHR DDP Disbursement\” + ‘ AND Account__c =\” + userAccountID +’\”;
        //soql+= ‘ order by ‘ + sortField + ‘ ‘ + sortDir + ‘ limit 20′;

        runQuery();
        }

        // toggles the sorting of query from ascdesc
        public void toggleSort() {
        //ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, ‘Inside Toggle’+soql + sortField +sortDir ));
        // simply toggle the direction
        sortDir = sortDir.equals(‘asc’) ? ‘desc’ : ‘asc';
        // run the query again
        runQuery();
        }

        // runs the actual query
        public void runQuery() {

        try {
        //ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, ‘Query before setController ‘+soql ));

        con = null;
        //soql = soql + ‘ order by ‘ + sortField + ‘ ‘ + sortDir + ‘ limit 20′;
        moneyTransactions = getMoneyTransactions();
        //moneyTransactions = Database.query(soql + ‘ order by ‘ + sortField + ‘ ‘ + sortDir + ‘ limit 20′);
        //ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, ‘After money transactions is obtained ‘+soql ));
        } catch (Exception e) {
        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, ‘Ooops! SOQL error ‘+soql ));
        }

        }

        // runs the search with parameters passed via Javascript
        public PageReference runSearch() {

        String fromDate = Apexpages.currentPage().getParameters().get(‘fromDate’);
        String toDate = Apexpages.currentPage().getParameters().get(‘toDate’);
        //ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR,fromDate+toDate ));
        //Date fromDate = Date.valueOf(fromDateStr);
        //Date toDate = Date.valueOf(toDateStr);
        //String accountName = Apexpages.currentPage().getParameters().get(‘accountName’);
        //String technology = Apexpages.currentPage().getParameters().get(‘technology’);

        //soql = ‘select ACH_Type__c, Settlement_Date_First__c, Total_ACH_Amount__c from Money_Transaction__c';

        //soql = ‘select ACH_Type__c, Settlement_Date_First__c, Total_ACH_Amount__c,Settlement__c from Money_Transaction__c where Settlement_Date_First__c = ‘ + fromDate + ‘ AND ACH_Type__c != \’VHR DDP Disbursement\” + ‘ order by ‘ + sortField + ‘ ‘ + sortDir + ‘ limit 20′;
        soql = ‘select ACH_Type__c, Settlement_Date_First__c, Total_ACH_Amount__c,Settlement__r.id,Settlement__r.name,Money_Movement_Type__c,Bank_Name__c,Bank_Account_Number__c,Tax_Batch__c,Payroll_Group_Detail__c from Money_Transaction__c where Settlement_Date_First__c = ‘ + fromDate + ‘ AND ACH_Type__c != \’VHR DDP Disbursement\”;
        con = new ApexPages.StandardSetController(Database.getQueryLocator(soql));
        //soql = ‘select ACH_Type__c, Settlement_Date_First__c,Total_ACH_Amount__c,Settlement__c from Money_Transaction__c where Settlement_Date_First__c = 2012-01-01′;
        /*if (!firstName.equals(”))
        soql += ‘ and firstname LIKE \”+String.escapeSingleQuotes(firstName)+’%\”;
        if (!lastName.equals(”))
        soql += ‘ and lastname LIKE \”+String.escapeSingleQuotes(lastName)+’%\”;
        if (!accountName.equals(”))
        soql += ‘ and account.name LIKE \”+String.escapeSingleQuotes(accountName)+’%\”;
        if (!technology.equals(”))
        soql += ‘ and interested_technologies__c includes (\”+technology+’\’)';*/

        // run the query again
        runQuery();

        return null;
        }

        // indicates whether there are more records after the current page set.
        public Boolean hasNext {
        get {
        return con.getHasNext();

        }
        set;
        }

        // indicates whether there are more records before the current page set.
        public Boolean hasPrevious {
        get {
        return con.getHasPrevious();
        }
        set;
        }

        // returns the page number of the current page set
        public Integer pageNumber {
        get {
        return con.getPageNumber();
        }
        set;
        }

        // returns the first page of records
        public void first() {
        //con = null;
        try{
        con.first();

        moneyTransactions = getMoneyTransactions();
        }
        catch (Exception e)
        {
        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, ‘Code Error ‘+e));
        }

        }

        // returns the last page of records
        public void last() {
        //con = null;
        try{
        con.last();
        moneyTransactions = getMoneyTransactions();
        }
        catch (Exception e)
        {
        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, ‘Code Error ‘+e));
        }
        }

        // returns the previous page of records
        public void previous() {
        //con = null;
        try{
        con.previous();
        moneyTransactions = getMoneyTransactions();
        }
        catch (Exception e)
        {
        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, ‘Code Error ‘+e));
        }
        }

        // returns the next page of records
        public void next() {
        try{
        //con = null;
        //if(con.getRecord()!=null)
        //{
        // con.save();
        //}
        con.next();
        moneyTransactions = getMoneyTransactions();
        }
        catch (Exception e)
        {
        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, ‘Code Error ‘+e));
        }

        }

  15. calvinrn says:

    Update: I was able to fix the issue with the next(), previous() etc wrt to calling the getRecords method. But the freezing up is still happening. When I click next and previous continuously for a few minutes, its slows down a lot and eventually freezes up.

  16. suresh says:

    Hi Srini,
    my problem is am displaying all contacts using pagination with command link. when i
    click on any record name it will show it’s detai page in visualforce page. here i put one more pagination. click on next, previous it’s working fine.

    But when i clicking on contact record name comparing with param id it will show it’s detail page but con having only one record next,previous not working.

    is there any way to solve this problem.

    Thank you.

  17. Arun says:

    Hi,

    I need a requirement which calculates no of records that are shown in enhancedlist(records are shown 50 out of 120) and according to that horizontalbar should be displayed.
    If i remove records from enhancedlist, the horizontalbar should update

    Can I know to do this.??

  18. Swati says:

    Awesome!!!

  19. Temple Hoff says:

    This seems to only work if you happen to have less than 10000 account records. What can you do if you have more? I’ve looked at ‘offset’ but that is for only very small records sets of less than 2000. I’ve also looked at a for query loop, but it doesn’t look like you can dynamically build a query statement for that. So, you do you exactly what you are doing above if you happen to have 20k or 30k account records?

  20. Pingback: Visualforce: Pagination – The next, previous etc links freeze up after clicking for a while | Joshua Pyle

  21. David G. says:

    Hi Srini,

    I like the things you are working on here in your blog…. I thought this might be of interest to you.

    We would like to build a “Next” & “Previous” Small hover / popup window that Links to a select List of Salesforce Records using the “selectList value” The functionality would be similar to the ActivityReminderPage? but linked to one Account record at a time. We would have a few field in the window like “Account.name, account.phone, and big picture fields be added in future.

    That’s just a high level overview…. I would like to talk more on this when you have the time.

    Thanks David

  22. Thanks says:

        Public List getAccounts(){
            List accList = new List();
            acclist=setCon.getRecords())
            return accList;
        }

    is returning a single value instead of list when i 10 records in the list

  23. Sampatti says:

    Hi Srini,
    I have a requirement to show the pagenumbers as command links on the VF page itself, and when it is clicked, it should be navigated to that page. Can this be achieved in this controller?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s