As an example, say you wanted to develop a Visualforce page for paginating over a set of Account records. As stated earlier, the ideal implementation for this use case is still to use a StandardSetController, but for the purposes of illustration lets see how you could implement this using the new OFFSET clause. You can find the entire code for this sample on GitHub, but lets focus on the Apex extension class for now.
Each time that the user clicks on the “Next’ or ‘Previous’ link on the page, the pageNumber variable is updated and the queryAccounts method is called. This is where the OFFSET magic happens. The OFFSET clause specifies the starting row offset into the result set returned by your query. So for example if your query returned a total of 30 Account records, adding a ‘ LIMIT 10 OFFSET 20’ clause would return rows 21-30. This is the exact logic implemented in the queryAccounts method.
A couple of additional notes on the use of the OFFSET clause
- This feature is currently in Developer Preview and you’ll need to contact Salesforce support to enable it in your Org.
- It is usually a best practice to use a ORDER BY clause when you use OFFSET (as shown above) to ensure that the result set ordering is consistent.
- Similarly, it is recommended that you use a LIMIT clause in combination with OFFSET if you need to retrieve subsequent subsets of the same result set. That’s how for example the queryAccounts method can keep getting the next/previous set of 10 Account records from the initial result set.
- OFFSET is applied to the result set returned at the time of the query. The paging results may change if the underlying data is modified (for example if someone inserts/deletes records) between multiple queries using OFFSET into the same result set. If this is of concern to your design, you’ll need to use the query/queryMore pattern of the API instead.