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>
Awesome!!
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.
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
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?
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
Yup this is now solved. I was also making a few other errors. Thanks a lot.
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 ?
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 🙂
Can we include this pagination as inline VF ?
Yes… you should be able to do it
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
Sumanth… until and unless I see the code, I can’t help you out 😉
Can this be used only on standard objects?
Can this StandardSetController be used only on standard objects?
This can work with custom objects also
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.
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.
@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 ?
Page which has the same set Controller
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
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,
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?
Without looking at your code, it is difficult to tell you what is wrong. Post your code here
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));
}
}
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.
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.
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.??
Awesome!!!
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?
Unfortunately, you have to build your own solution. You can use extJS or any other javascript framework
Pingback: Visualforce: Pagination – The next, previous etc links freeze up after clicking for a while | Joshua Pyle
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
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
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?