Pagination of an Infinite List of Records in Salesforce: Challenges and Resolution Methods

Author: Ievgen Kyselov, Salesforce developer at NIX 

This pagination approach is not new or uncommon, but it's rarely discussed in detail. What I am showcasing differs from the methods you might find through a simple Google search.

What is Pagination?

Put simply, it's page-by-page navigation. It's a way to display a large amount of homogeneous information by dividing the content into pages. Many Salesforce developers, myself included, often encounter pagination when displaying a significant volume of data on the user interface. On one of my projects, we were presenting phone numbers in a data table. However, in certain cases, the data wouldn't display, as the information retrieval took too long. Users were unable to access any data. So, why did this happen?

1. Contacts were selected through several nested database queries.

We were dealing with multiple levels of parent-child relationships between Contacts and child objects. Due to business logic requirements, we needed to filter contacts based on filters applied both to the contacts themselves and to filters applied to the child objects of the contacts.

Example of Parent-Child Relationship

2. A vast number of records in Contacts (several hundred thousand) and their child objects.

To elucidate the necessity of the pagination approach I've chosen, I'll list and compare four other methods proposed by the Salesforce platform:

• Pagination using the List Controller for Visualforce pages. 
• Pagination using the Database.getQueryLocator class.
• Pagination employing SOQL query and the OFFSET operator. 
• Pagination via Apex code to retrieve all parent records into a single list using an SOQL query. Subsequently, the necessary records can be selected from this list according to the page.

The first three tools didn't suit me for the following reasons:

• The List Controller for Visualforce pages is not applicable for LWC components and has a limited number of records it can process – 10,000 records.
• getQueryLocator also has a limit of 10,000 records and isn't compatible with the task's requirements. 
• The SOQL query with the OFFSET operator is limited to 2,000 records that can be provided with an OFFSET in the query. Therefore, it can't be used for pagination in the case of a large amount of data.

Let's take a closer look at the 4th pagination method. And let's highlight an important detail upfront – we don't have the ability to fetch all data, as there's a limit of 50,000 records that we can retrieve across all queries within a single transaction.

What does this lead to? If we query child records, based on which we subsequently query contacts, we might obtain, for example, 47,000 total child records. But then we can only retrieve 3,000 contacts. Even if there are actually more, let's say 6,000. Essentially, we're providing the user with knowingly inaccurate data. They won't know how many records they can actually get in the data table. They won't see part of the Contacts and won't interact with them, assuming they don't have all the data. I call this the "User's Data Iceberg."

User Data Iceberg

In this way, the user receives distorted and incomplete information, which will negatively impact their data work.

The second point is that aggregating data into lists from nested database queries takes a lot of time. This leads to exceeding the CPU Time limit. As a result, the user doesn't receive any data at all. We can't reduce the processing time for nested queries or overcome the limits on records in a single transaction. We are constrained by the limitations imposed by the Salesforce database (I will explain how these limits can be circumvented later). Therefore, I decided to reduce the number of records in the query for the contacts themselves to at least shorten the time for data retrieval and processing. So, if I previously wrote a query for contacts:

SELECT Id FROM Contact WHERE Id IN :ids ORDER BY Next_Contact_Date_Time__c LIMIT 50000

where ids is a list including the Ids of child records from the nested query, now I need to write:

SELECT Id FROM Contact WHERE Id IN :ids ORDER BY Next_Contact_Date_Time__c LIMIT 50

limiting the number of contacts per page to 50.

This allowed me to reduce the overall time the code works on retrieving the necessary records for the user. It also allows me to fetch either all Contact records or a significantly larger portion than in the first query.

However, this is only the first 50 contacts, and I need all contacts for the data table…

What's Next?

On some resources, they suggest ordering records by a specific field, like ID. Then pagination is done by comparing the field's value with the value of the last or first record on the previous page. It all depends on the direction of pagination.

The ideas I came across were either not fully developed or not sufficiently elaborated for a more general application. Moreover, such a method (as far as I can judge from personal experience) is quite rarely used. The majority of recommendations online concern the application of one of the 4 pagination tools mentioned earlier. In my opinion, this is unjustified. Therefore, I took this idea into consideration and practically developed it for many cases of code implementation.

How Does it Work?

• For "paging" from the previous to the next page, it is necessary to order the records in each database query by one of the fields (e.g., ID) in ascending order. In the query condition, it is specified that the value of this field for N records on the next page should be greater than the value of the field for the last record on the previous page.

• For "paging" back, it is necessary to order the records in each database query by one of the fields in descending order. Now, in the query condition, the value of the field for N records on the next page should be smaller than the value of the field for the first record on the previous page.

The first and last pages somewhat deviate from this concept. The first page lacks a previous page because there isn't one, and the last page lacks a next page. Also, the last page almost always contains fewer records than all the previous ones.

How Does this Concept Look in Code?

I'll provide an example below. Please note that the methods below have a fixed page size of 50 records. If you need a different size, you can replace 50 with the value you need. Alternatively, you can introduce an additional parameter into the methods where you pass the required page size.

I used two methods related to an LWC component that contains a data table. The filters intended for querying contacts from the database were stored in records of a separate object. However, you can use a JSON object generated by you in the LWC component's code instead.

The first method 'getFirstPage' of the LWC component is designed to retrieve records for the first page during the initial load of the table:

/** LWC component method that initially retrieves the data for the first page */
getFirstPage(event){
/** Imported method to component @salesforce/apex/ApexClassName.getContacts 
* Parameters:
* recId: filterRecordId - ID of the record that contains filters for retrieving contacts
* pageRecords: null - array of the records IDs from the page to pass them into the Apex methods as the previous page IDs, it has the NULL value when the first page is loaded first time as we have no previous page yet
* comparingSign: null - the symbol for the copmarison of the records by the field which is used for ordering the records(contacts), it has the NULL value when the first page is loaded the first time
* order: ordering symbol which can be 'ASC' or 'DESC' depending on your wishes
*/
/* The variable that defines in ASC or DESC order records should be sorted. You can use some drop-down menu on the UI for selecting it. */
     let currentSorting = event.detail.sortingOrder;
     getContacts({recId: filterRecordId, pageRecords: null, comparingSign: null, order: currentSorting, currentPage: 1, sortingOrder:        currentSorting})
     .then((result) => {
          if(result.contacts.length > 0){
                    //the first page contacts
                    this.contacts = [...this.result.contacts];  
                    //the total pages count
                    this.totalPages = this.result.totalPages;  
                    /** The returned number of the first page.
                     * You can modify and assign the first page directly if you want.*/
                    this.currentPage = this.result.currentPage; 
                    /* Your code that processes the data */
          }
          else{
                 this.contacts = [];
                 this.totalPages = 0;
                 this.currentPage = 0;
          }
}
.catch((error) => {
     console.log(error);
     console.log('error due to request' + error);
}); 
}

The provided method of the component calls the Apex class method getContacts, which returns an object with contacts for the initial page.

Don't forget to check out: Delete Multiple Records Using Pagination in Lightning Component

The second method, handlePageChange, of the LWC component is intended for event handling. Specifically, it handles user clicks on control buttons to navigate to the next, previous, last, and first pages after the initial first page has been retrieved.

/** LWC method that retrieves the data when on of the pagination buttons (next page/previous page/last page/fist page) is clicked */
handlePageChange = (message) =>{
/* The variable that defines in ASC or DESC order records should be sorted. You can use some drop-down menu on the UI for selecting it. */
let currentSorting = message.sortingOrder; 
/* Selecting the comparison sign and ordering direction depending on the pagination direction */
let compSign;
let sortOrder;
if(currentSorting = 'ASC'){
     compSign = this.currentPage > message.currentPage ? '<' : '>';
     sortOrder = this.currentPage > message.currentPage ? 'DESC' : 'ASC';
}
else{
     compSign = this.currentPage > message.currentPage ? '>' : '<';
     sortOrder = this.currentPage > message.currentPage ? 'ASC' : 'DESC';
}
/* Combining the record IDs from the page to pass them into the Apex methods as the previous page IDs */
let contIds = [];
this.contacts.forEach(cont => {
    contIds.push(cont.contactId);
});
let conditions = {};
conditions.recId = this.dialListId;
/** Selecting the comparison sign and ordering direction depending on the pagination direction and checking if the next page is the first/last page */
if(message.currentPage == 1 || message.currentPage == this.totalPages){
     conditions.pageRecords = null;
     conditions.comparingSign = null;
     if(currentSorting = 'ASC'){
          conditions.order = message.currentPage == 1 ? 'ASC' : 'DESC';
     }
     else{
          conditions.order = message.currentPage == 1 ? 'DESC' : 'ASC';
     }
}
else{
conditions.pageRecords = contIds;
if(currentSorting = 'ASC'){
     conditions.comparingSign = this.currentPage > message.currentPage ? '<' : '>';
     conditions.order = this.currentPage > message.currentPage ? 'DESC' : 'ASC';                
}
else{
     conditions.comparingSign = this.currentPage > message.currentPage ? '>' : '<';
     conditions.order = this.currentPage > message.currentPage ? 'ASC' : 'DESC';
     }
}
this.currentPage = message.currentPage;
/* Imported method to component @salesforce/apex/ApexClassName.getContacts */
getContacts({recId: conditions.recId, pageRecords: conditions.pageRecords, 
comparingSign: conditions.comparingSign, order: conditions.order, 
currentPage: message.currentPage, sortingOrder: currentSorting})
.then((result) => {
     this.contacts = this.formatContacts(result.contacts);
     this.totalPages = result.totalPages;
                /* < Your code that processes the data > */
})
.catch((error) => {
    console.log(error);
    console.log('error due to request' + error);
    });
}

The handlePageChange method of the component also calls the Apex class method getContacts, which returns an object with contacts for the initial first page.

It's worth noting that due to the existing code and to maintain similarity between different interfaces, I've used two methods. However, you can slightly modify the second handlePageChange method and use only that method to load the initial page.

The Apex class mentioned in the code of the LWC component as ApexClassName has the following methods:

1. getContacts:

/** LWC method that retrieves the data when on of the pagination buttons (next page/previous page/last page/fist page) is clicked */
handlePageChange = (message) =>{
/* The variable that defines in ASC or DESC order records should be sorted. You can use some drop-down menu on the UI for selecting it. */
let currentSorting = message.sortingOrder; 
/* Selecting the comparison sign and ordering direction depending on the pagination direction */
let compSign;
let sortOrder;
if(currentSorting = 'ASC'){
     compSign = this.currentPage > message.currentPage ? '<' : '>';
     sortOrder = this.currentPage > message.currentPage ? 'DESC' : 'ASC';
}
else{
     compSign = this.currentPage > message.currentPage ? '>' : '<';
     sortOrder = this.currentPage > message.currentPage ? 'ASC' : 'DESC';
     }
/* Combining the record IDs from the page to pass them into the Apex methods as the previous page IDs */
let contIds = [];
this.contacts.forEach(cont => {
     contIds.push(cont.contactId);
});
let conditions = {};
conditions.recId = this.dialListId;
/** Selecting the comparison sign and ordering direction depending on the pagination direction and checking if the next page is the first/last page */
if(message.currentPage == 1 || message.currentPage == this.totalPages){
     conditions.pageRecords = null;
     conditions.comparingSign = null;
     if(currentSorting = 'ASC'){
     conditions.order = message.currentPage == 1 ? 'ASC' : 'DESC';
     }
     else{
     conditions.order = message.currentPage == 1 ? 'DESC' : 'ASC';
     }
}
else{
     conditions.pageRecords = contIds;
     if(currentSorting = 'ASC'){
          conditions.comparingSign = this.currentPage > message.currentPage ? '<' : '>';
          conditions.order = this.currentPage > message.currentPage ? 'DESC' : 'ASC';                
     }
     else{
         conditions.comparingSign = this.currentPage > message.currentPage ? '>' : '<';
         conditions.order = this.currentPage > message.currentPage ? 'ASC' : 'DESC';
     }
}
this.currentPage = message.currentPage;
/* Imported method to component @salesforce/apex/ApexClassName.getContacts */
getContacts({recId: conditions.recId, pageRecords: conditions.pageRecords, 
comparingSign: conditions.comparingSign, order: conditions.order, 
currentPage: message.currentPage, sortingOrder: currentSorting})
.then((result) => {
     this.contacts = this.formatContacts(result.contacts);
     this.totalPages = result.totalPages;
     /* < Your code that processes the data > */
})
.catch((error) => {
     console.log(error);
     console.log('error due to request' + error);
});
}

This method simply passes parameters to the getData method and returns the processed result to the LWC component as an object of the PayLoad class.

2. getData:

/** The method that retrieves the IDs of the child records and prepares the part of the input parameters for the method getRecords that retrieves contacts */
public static List<Contact> getData(String recId, List<String> pageRecords, String comparingSign, String order, Integer pageNumber, String sortingOrder){
     List <Id> ids = new List <Id>();
/* Some custom code that retrieves the IDs of the child records using the filtering logic saved in the record with the id='recId' and adds them to the 'ids' variable*/
/** Instead of the 'recId' and saved logic in the record of some SObject you can pass filtering logic inside JSON object for example. It depends on how you want to build your application */
     String idAsString = idSetAsSting(ids);
//This is the conversion of the list of IDs into a string 
/* the countQuery string is to define the total scope of the contacts that are corresponding to our condition. Here you can use your own condition for the definition of the records total count */
     String countQuery = 'SELECT count() FROM  Contact WHERE Id IN ' + idAsString; 
     totalRecordsCount = database.countQuery(countQuery);
/** The queryLimit is the required parameter  for specifying the number  of records per page. This is required because the last page may have a  different quantity of records than  the other pages have */
     Integer queryLimit = findCurrentLimit(totalRecordsCount, pageNumber);
     String query = 'SELECT Id, ' + CONTACT_FIELDS + ' FROM Contact' + ' WHERE Id IN ' + idAsString;
/** The previous page Contacts are required to compare the last or the first record ID depending on pagination direction */
     String queryPreviousPage = 'SELECT ID,Next_Contact_Date_Time__c FROM Contact WHERE ID IN :pageRecords ORDER BY Id ' + sortingOrder;
     List<Contact> previousContacts = database.query(queryPreviousPage);
/** The next string is the contacts retrieved for the page */
     List<Contact> contacts = getRecords(previousContacts, comparingSign, order, queryLimit, query, 'Id', sortingOrder);
     return contacts;
}

The getData method is used for:

• Executing all nested queries in the database (if necessary, along with associated logic).
• Generating a list of contact IDs that satisfy the search results within the executed nested queries (this list is denoted by the variable 'ids').
• Formulating parameters for retrieving records of the current page, namely: queryLimit — the number of records displayed per page; previousContacts — a list of records (in my case, a list of contacts).

Obtaining the number of records displayed per page is necessary to limit the records for the last page. This ensures that the sequence of records is not disrupted when paging from the previous to the next.

Obtaining records of the previous page, previousContacts, through a SOQL query is not mandatory. This is convenient when you are working with relatively static data that doesn't change too frequently. Additionally, this slightly reduces the amount of information transmitted to the server for further processing. In other cases, it's better to directly pass the data list from the page. It's important to consider the possibility of changing the position of records on the page or moving records to other pages when modifying data within the records. By the way, this consideration also applies to pagination using other methods.

3. getRecords:

/** The method that retrieves contacts for the current page.
* Its input parameters are: 
* pageRecords - the list of the records from the previous page
* comparingSign - one the signs '>' or '<'
* order - the order in which the records are ordered in the particular request
* newLimit - the quantity of the records for the current page
* query - the query with filters that will be modified to get the records for the current page
* orderBy - the name of the object field according to which the records are ordered
* sortingOrder - the order in which the records are ordered for the pages 
*/
public static List<SObject> getRecords(List<SObject> pageRecords, 
String comparingSign, String order, Integer newLimit, String query, String orderBy, String sortingOrder){
String lastId; //the variable that stores the ID that will be used in the query for comparison
String orderByString = orderBy; //the necessity of the orderByString variable will be explained further 
String firstQuery = query; //the necessity of the firstQuery variable will be explained further 
if(pageRecords != null && !pageRecords.isEmpty()){
     if(order == sortingOrder){
     //if records are ordered in ascending order the lastId equals to the ID of the last record from the previous page
     lastId = String.valueOf(pageRecords[pageRecords.size() - 1].get(orderByString));
     }
else{
     //if records are ordered in descending order the lastId equals to the ID of the first record from the previous page
     lastId = String.valueOf(pageRecords[0].get(orderByString));
}
lastId = '\'' + lastId + '\'';
}
//if the current page is not the first or the last then we need to add a comparison substring to the query
if(lastId != null && comparingSign != null){
     //but first we need to check  that query contains keyword WHERE
     if(query.toLowerCase().substringAfterLast('from').contains('where')){
     query = query + ' AND ' + orderByString + ' ' + comparingSign + ' ' + lastId;
     }
else{
     query = query + ' WHERE ' + orderByString + ' ' + comparingSign + ' ' + lastId;
    }
} 
//adding the ordering by the field to the query
query = query + ' ORDER BY ' + orderByString + ' ' + order + ' LIMIT ' + newLimit;
//querying  the records
Map<Id, SObject> records = new Map<Id,SObject>((List<SObject>)Database.query(query));
List<SObject> recordsToReturn = new List<SObject>();
//if there are queried records then sorting them in ascending order
if(records.size() > 0) recordsToReturn.addAll(sortByIdAndSortingOrder(records, orderByString, sortingOrder));
return recordsToReturn; //the returned records
}

The getRecords method fetches records of one page and presents them in the order specified by the user. In the first and last pages, the variable 'lastId' is ignored. The query doesn't perform a comparison but simply constructs the records in ascending (ASC) or descending (DESC) order. It always displays the first page, but with records ordered in ASC or DESC. The 'sortByIdAndSortingOrder' method needs to be invoked to ensure the delivery of records in the desired order.

4. sortByIdAndSortingOrder:

/** The method that guaranteed returns the records in the ascending  order ordering them by orderBy field */
public static List<SObject> sortByIdAndSortingOrder(Map<Id, SObject> pageRecords, String orderBy, String sortingOrder){
Set<ID> idSet = new Set<ID>();
for (ID recId : pageRecords.keySet()) {
     idSet.add(recId);
}
String sObjName = pageRecords.values()[0].Id.getSObjectType().getDescribe().getName();
String rightOrderQuery = 'SELECT Id FROM ' + sObjName + ' WHERE Id in :idSet ORDER BY ' + orderBy + ' ' + sortingOrder;
List<SObject> records = Database.query(rightOrderQuery);
List<SObject> recordsToReturn = new List<SObject>();
for (SObject obj : records) {
     recordsToReturn.add(pageRecords.get(obj.Id));
}
return recordsToReturn;
}

The 'sortByIdAndSortingOrder' method is purely utilitarian. Its purpose is to ensure the delivery of records gathered in the required order.

5. idSetAsSting

  /** The method transforms the list of IDs into a string with quotes and brackets */
public static String idSetAsSting(List<String> ids){
String stringSet = '(';
if(!ids.isEmpty()){
     for(String id : ids) {
          stringSet = stringSet + '\'' + id + '\'' + ',';
     }            
}
else{
     stringSet = stringSet + '\'' + '\'';
}
stringSet = stringSet.removeEnd(',') + ')';
return stringSet;
}

This method is also utilitarian. It is necessary to convert the list of IDs into a string for querying the database.

6. findCurrentLimit:

/** The method defines the limit of the records for the current page request */
public static Integer findCurrentLimit(Integer totalRecords, Integer pageNum)
Double pagesCount = Double.valueOf(totalRecords);
Double totalPages = Decimal.valueOf(pagesCount/50).round(System.RoundingMode.UP);
Integer queryLimit = pageNum == Integer.valueOf(totalPages) ? totalRecords - ((Integer.valueOf(totalPages) - 1) * 50): 50;
return queryLimit;
}

The method 'findCurrentLimit' indicates the record limit for the page and determines their count for the last page.

The approach described uses the sorting of records by ID. In my case, as in most others, records are sorted by a specific field. For my example, this is the 'Next Contact Date Time' field with a datetime data type.

The issue with such non-system fields is that in practice, many records have NULL values for this field. Therefore, records cannot be sorted by this field. Thus, when trying to sort by a non-system field in ascending order, records with a NULL value in this field are likely to appear first in the overall list of all records in the database.

When sorting records by a non-system field, it's worth splitting all records into two parts: records with a field value equal to NULL, and records with a field value that is not equal to NULL.

For each of the two segments within the overall list of object records, we will apply the aforementioned mechanism separately. For records where the field value is NULL, we can use sorting by ID, and for the second case, we will sort by that field.

A distinct case is a page that includes records with both filled and NULL field values. This page is common when dividing all records into pagination pages in most scenarios.

The entire list of retrievable records from the database is divided into three types of pages based on the values of the non-system field used for sorting the records:

• Pages where all records have only the NULL value in the field.
• Pages where all records have a non-NULL field value.
• A page where some records have the NULL value in the field, while the rest have different values.

Below, I will provide the changes in the code for the getData and getRecords methods that take into account these described nuances.

In this article, I attempted to systematize my practices and described a solution to the pagination problem using a rare technique based on comparing records in one of the fields.

In my opinion, this approach has the following advantages:

• Fast query processing when navigating to a page. This helps prevent exceeding the CPU Time limit in many cases. Additionally, when there are no nested queries to retrieve records, it allows complete avoidance of limit exceedances.
• When queries do not have nested sub-queries, it allows viewing all records without being restricted by Salesforce's 50,000-record limit. Personally, I've viewed around 400,000 records, but even more is possible.
• Enhanced reliability of transmitted data. Users receive comprehensive information about the number of accessible records according to selected filters.
• The method can be applied to both LWC and Visualforce.

Check out another amazing blog here: Salesforce Visualforce - Custom Pagination Without Using Offset in Salesforce

During the course of development, I identified some drawbacks as well:

• If nested queries are present, you still need to limit the total number of records to 50,000.
• For records obtained through nested queries, it's advisable to set a limit with a margin based on the number of main object records displayed on a page. This prevents exceeding the 50,000-record limit per transaction. It's recommended to use an additional variable in the methods representing the number of records per page, which would be subtracted from the 50,000-record limit.
• Inaccuracy in data volume due to nested queries.

This follows from the previous point. If the number of records retrieved through nested queries is restricted, it limits the search zone for the object that interests the user.

• More complex logic compared to traditional pagination methods. This includes the need to handle NULL values in non-system fields.

As an even more complex alternative, the sequence of executing Apex Batchable classes could be mentioned. The overall result would be the records retrieved for a single page. Meanwhile, the client-side (browser) code would periodically send requests to retrieve these records. Until the server-side code completes and the requested records become available, the user's page might be blocked for further actions or switches. A similar logic could be implemented considering the platform's event technology.

However, this is not the end. I will delve into the implementation of this batch-based pagination method in one of the upcoming articles. For now, I hope everyone enjoys applying the practices I've described!