The SOQL OFFSET clause is now generally available in the Summer ’12 version of Salesforce. This long awaited feature is very exciting and powerful. Using the OFFSET clause you can easily build Pagination into your Visualforce Pages. Before, a Salesforce Developer had to rely on other inefficient methods to accomplish pagination.
The Salesforce release notes discusses “large” sets of data but our disclaimer on that statement is “your offset value cannot be greater than 2000”. So if your definition of “large” is more than 2000, you’ll need to add a filter or checkout the queryMore() functionality.
Before we look at any code, we’ve built a working demo to showoff the OFFSET clause’s functionality: SOQL OFFSET Example. This is example is running on our production Salesforce org using a public Force.com site. We just created a custom object called Number__c and exposed it to the anonymous user.
Now that you see how pagination looks and works in a real demo, let’s discuss the code for the Visualforce Page. The page is actually very simple as it only includes 4 commandButtons and a pageBlockTable. The 4 buttons act as VCR type controls allowing the user to page up and down through the result set or go to the end or beginning of the set. Here’s the code for the page:
<apex:page title="Salesforce SOQL Offset Example Using Visualforce" controller="soql_offset_example" showHeader="false" sidebar="false" readOnly="true" cache="false">
<apex:sectionHeader subtitle="SOQL Offset Example" title="Square Root Table"/>
<apex:pageBlock >
<apex:pageBlockButtons location="top" >
<apex:outputPanel id="myButtons">
<apex:form >
<apex:commandButton action="{!Beginning}" title="Beginning" value="<<" disabled="{!disablePrevious}" reRender="myPanel,myButtons"/>
<apex:commandButton action="{!Previous}" title="Previous" value="<" disabled="{!disablePrevious}" reRender="myPanel,myButtons"/>
<apex:commandButton action="{!Next}" title="Next" value=">" disabled="{!disableNext}" reRender="myPanel,myButtons"/>
<apex:commandButton action="{!End}" title="End" value=">>" disabled="{!disableNext}" reRender="myPanel,myButtons"/>
</apex:form>
</apex:outputPanel>
</apex:pageBlockButtons>
<apex:pageBlockSection columns="1">
<p>This Visualforce Page demonstrates the usage of the "OFFSET" clause in a SOQL SELECT statement. You can "Paginate" through
a large result set quickly. This example allows a user to paginate up and down through a result set 20 records at a time.
Using the "VCR" buttons above, you can also quickly skip to the end or the beginning of the set.</p>
<p>The accompanying code can be viewed here: <a href="http://blog.redpointsolutions.com/bid/182738/Add-Pagination-to-your-Visualforce-Pages-using-the-SOQL-OFFSET-Clause" target="_blank">RedPoint Solutions BLOG</a></p>
</apex:pageBlockSection>
<apex:pageBlockSection title="Numbers and their Square Roots (Total List Size: {!total_size})" collapsible="false">
<apex:outputPanel id="myPanel">
<apex:pageMessages id="theMessages" />
<apex:pageBlockTable value="{!numbers}" var="n" align="center">
<apex:column value="{!n.Number__c}" />
<apex:column value="{!n.Square_Root__c}" />
<apex:facet name="footer">Showing Page # {!pageNumber} of {!totalPages}</apex:facet>
</apex:pageBlockTable>
</apex:outputPanel>
</apex:pageBlockSection>
</apex:pageBlock>
</apex:page>
As you can see the page is fairly simple. Now let’s discuss the Apex Controller Class.
The OFFSET clause is really easy to add to your SOQL statement. Here’s a basic example:
SELECT Id, Name FROM Account ORDER BY Name LIMIT 10 OFFSET 100
This above query returns accounts 101 through 110. Being able to add pagination to Visualforce pages is getting more and more important with the rise in popularity of mobile devices. In the past, scrolling through 1000 rows on a large monitor was not annoying. Scrolling through 1000 rows on a mobile device can be cumbersome and irritating.
Back to our example. There is a little bit more to the controller but nothing too intense. Once you see how the OFFSET clause works along with LIMIT and ORDER BY, you’ll be adding pagination your pages in no time! In our example, we have set the number of rows for each page to 20. When a user clicks the “Next” button, they will paginate to the next list of 20 rows. When you click “Back” you go backwards through the list.
We’ve added functionality to disable the appropriate buttons when you arrive at the end of or beginning of the list. To play with this example in your own Salesforce Org, you’ll just need to create a custom object called Number__c and add the fields that you want to view on your page. If you have questions or need help, feel free to contact us.
public with sharing class soql_offset_example {
private integer counter=0; //keeps track of the offset
private integer list_size=20; //sets the page size or number of rows
public integer total_size; //used to show user the total size of the list
public soql_offset_example() {
total_size = [select count() from Number__c]; //set the total size in the constructor
}
public Number__c[] getNumbers() {
try {
Number__c[] numbers = [select Number__c, Square_Root__c
from Number__c
order by Number__c
limit :list_size
offset :counter];
return numbers;
} catch (QueryException e) {
ApexPages.addMessages(e);
return null;
}
}
public PageReference Beginning() { //user clicked beginning
counter = 0;
return null;
}
public PageReference Previous() { //user clicked previous button
counter -= list_size;
return null;
}
public PageReference Next() { //user clicked next button
counter += list_size;
return null;
}
public PageReference End() { //user clicked end
counter = total_size - math.mod(total_size, list_size);
return null;
}
public Boolean getDisablePrevious() {
//this will disable the previous and beginning buttons
if (counter>0) return false; else return true;
}
public Boolean getDisableNext() { //this will disable the next and end buttons
if (counter + list_size < total_size) return false; else return true;
}
public Integer getTotal_size() {
return total_size;
}
public Integer getPageNumber() {
return counter/list_size + 1;
}
public Integer getTotalPages() {
if (math.mod(total_size, list_size) > 0) {
return total_size/list_size + 1;
} else {
return (total_size/list_size);
}
}
}