Sidestep the 2000 record limit in server-side pagination using Apex

Have you ever tried implementing server-side pagination using Apex with Salesforce SOQL limit and offset clauses? If yes, then you might already know that Salesforce SOQL doesn’t support offset values more than 2000. That means you can’t load more than 2000 records even if they are spread across multiple pages.

In this article, I’ll show you an approach that doesn’t use the SOQL offset clause, thereby overcoming this limitation for large data sets.

The Solution

Overview

The key part of the solution is to pass the Id of the last or the first record from the current page based on whether we are trying to load the next or the previous page of records. Once we have the Id of the record for an already loaded page, page size and direction, we can use SOQL with filters on the Id field, limit and order by clauses to load the next set of records. Let’s see some code to understand this further:

@RemoteAction
public static Map<String, Object> fetchRecords(String sObjectName, String lastKnownId, Integer pageSize, Integer direction) {
…
}

In the above method, we are accepting four parameters:

1. sObjectName: Used to specify the name of the object whose records we want to load
2. lastKnownId: This parameter can have different values depending upon which page and in what direction we are trying to load the records. For e.g. when loading the first page, this parameter will be null or an empty string with direction = 1; For loading the last page, this parameter will be null or an empty string with direction = -1; For loading the next page for the current page, this parameter will be the Id of the last record on the current page with direction = 1; For loading the previous page for the current page, this parameter will be the Id of the first record on the current page with direction = -1
3. pageSize: Used to specify the number of records to load per page
4. direction: Used to specify whether to load the next or the previous page

Details

Get the total numbers of records

First of all, we find the total number of records for the specified object in the system. We can do that using the following method:

public static Integer getTotalCount(String sObjectName) {
    Integer totalCount = 0;
    String query = 'select count(Id) totalCount from ' + sObjectName;
    List<AggregateResult> res = (List<AggregateResult>) Database.query(query);
    if (!res.isEmpty()) {
        totalCount = (Integer) res.get(0).get('totalCount');
    }
    return totalCount;
}

Calculate the total pages based on the total records and the page size

Next, we calculate the total number of pages based on the total number of records and the specified page size. We can use the following formula to calculate that:

Integer totalPages = (Integer) Math.ceil((Decimal) totalRecords / pageSize);

The key thing to note here is the usage of Math.ceil method to round the argument to the nearest Decimal which is not less than the argument.

Special Case: When starting from the last page

If the user wants to navigate to the last page of records, we support this use case by passing lastKnownId = null and direction = -1. We calculate the number of records on the last page based on the page size passed from client-side and return only that many records in the response.

public static Integer getLastPageRecordCount(Integer totalRecords, Integer pageSize) {
    Integer totalPages = (Integer) Math.ceil((Decimal) totalRecords / pageSize);
    Integer recordCount = totalRecords - ((totalPages - 1) * pageSize);
    return recordCount;
}

Load records

Next, we build a SOQL query to fetch the records from the Salesforce database. We are using Schema describe methods to find all the fields accessible to the logged in user to build a list of fields. Then they are embedded in the select clause and specify the object name in the from clause of the SOQL query.

Now, we need to understand how the direction flag is used with lastKnownId. If the lastKnownId value is not blank, then based on the direction flag we will either load the next or previous set of records by using the greater or less than operators.

Another key thing to note is that we are querying one extra record per page. This will be used to deduce values for two flags; i.e. hasNext and hasPrevious and set them in the response. This will help us identity whether current page has a next or a previous page.

Lastly, we are ordering records in ascending or descending order based on the direction the user is trying to navigate. For e.g. when the user is navigating from the last page to its previous page, we pass direction = -1. When direction = -1, we order the records in descending order and limit the number of records to page size + 1. This gives the user the ability to paginate forward and backward in the pages.

public static List<SObject> queryRecords(String sObjectName, String lastKnownId, Integer maxResults, Integer direction) {
    List<String> fields = describeSObjectFields(sObjectName);
    String query = 'select ' + String.join(fields, ',');
    query += ' from ' + sObjectName;
    if (String.isNotBlank(lastKnownId)) {
        query += ' where Id ' + (direction == 1 ? '>' : '<') + ' \'' + lastKnownId + '\' ';
    }
    if (maxResults != null) {
        // query one extra record
        maxResults =  maxResults + 1;
        query += ' order by Id ' + (direction == 1 ? 'asc' : 'desc') + ' limit :maxResults';
    }
    return Database.query(query);
}

Deduce the page state

Once we have queried the records for the next or the previous page, we set the hasNext, hasPrevious, firstId, lastId properties in the response based on the number of records loaded on the page using the getPageState method in SObjectQueryService class. The idea behind setting the firstId and lastId properties in the response is to make the client side implementation easier. For e.g. if we use any kind of sorting mechanism on the client side, we might lose track of the original order of the records. These properties help easy access to those Ids and navigation between pages.

Sort the records

One last thing to note is that when the direction flag = -1, we are sorting the records before setting them into the response. The idea is to sort the records by Id in ascending order since we are retrieving the records sorted in descending order when navigating backwards.

public class LoadRecordsCtrl {

    @RemoteAction
    public static Map<String, Object> fetchRecords(String sObjectName, String lastKnownId, Integer pageSize, Integer direction) {
        Map<String, Object> result = new Map<String, Object>();
        try {
            SObject sObj = (SObject) Type.forName(sObjectName).newInstance();
            Integer totalRecords = SObjectQueryService.getTotalCount(sObjectName);
            Integer totalPages = (Integer) Math.ceil((Decimal) totalRecords / pageSize);
            Integer maxResults = pageSize;
            if (String.isBlank(lastKnownId) && direction == -1) {
                // when querying the last page, get the number of records on last page
                maxResults = SObjectQueryService.getLastPageRecordCount(totalRecords, pageSize);
            }
            List<SObject> records = SObjectQueryService.queryRecords(sObjectName, lastKnownId, maxResults, direction);
            // remove extra record and set next / previous state
            result.putAll(SObjectQueryService.getPageState(records, lastKnownId, totalPages, pageSize, direction));
            // sort and set records
            if (direction == -1) records.sort();
            result.put('totalRecords', totalRecords);
            result.put('records', records);
            result.put('status', true);
        } catch (Exception ex) {
            result.put('status', false);
            result.put('error', ex.getMessage());
        }
        return result;
    }
}

public with sharing class SObjectQueryService {

    public static Integer getTotalCount(String sObjectName) {
        Integer totalCount = 0;
        String query = 'select count(Id) totalCount from ' + sObjectName;
        List<AggregateResult> res = (List<AggregateResult>) Database.query(query);
        if (!res.isEmpty()) {
            totalCount = (Integer) res.get(0).get('totalCount');
        }
        return totalCount;
    }

    public static Integer getLastPageRecordCount(Integer totalRecords, Integer pageSize) {
        Integer totalPages = (Integer) Math.ceil((Decimal) totalRecords / pageSize);
        Integer recordCount = totalRecords - ((totalPages - 1) * pageSize);
        return recordCount;
    }

    public static List<SObject> queryRecords(String sObjectName, String lastKnownId, Integer maxResults, Integer direction) {
        List<String> fields = describeSObjectFields(sObjectName);
        String query = 'select ' + String.join(fields, ',');
        query += ' from ' + sObjectName;
        if (String.isNotBlank(lastKnownId)) {
            query += ' where Id ' + (direction == 1 ? '>' : '<') + ' \'' + lastKnownId + '\' ';
        }
        if (maxResults != null) {
            // query one extra record
            maxResults =  maxResults + 1;
            query += ' order by Id ' + (direction == 1 ? 'asc' : 'desc') + ' limit :maxResults';
        }
        return Database.query(query);
    }

    public static Map<String, Object> getPageState(List<SObject> records, String lastKnownId, Integer totalPages, Integer pageSize, Integer direction) {
        Map<String, Object> pageState = new Map<String, Object>();
        if (String.isBlank(lastKnownId)) {
            if (records.size() > pageSize) {
                records.remove(records.size() - 1);
            }
            if (direction == -1) {
                // loading last page
                pageState.put('hasPrevious', totalPages > 1);
                pageState.put('hasNext', false);
            } else {
                // loading first page
                pageState.put('hasPrevious', false);
                pageState.put('hasNext', totalPages > 1);
            }
        } else if (records.size() > pageSize) {
            records.remove(records.size() - 1);
            pageState.put('hasPrevious', true);
            pageState.put('hasNext', true);
        } else {
            if (direction == -1) {
                // reached first page while navigating backwards
                pageState.put('hasPrevious', false);
                pageState.put('hasNext', true);
            } else {
                // reached last page while navigating forwards
                pageState.put('hasPrevious', true);
                pageState.put('hasNext', false);
            }
        }
        if (!records.isEmpty()) {
            // set first and last Id on the page
            if (direction == -1) {
                pageState.put('firstId', records.get(records.size() - 1).get('Id'));
                pageState.put('lastId', records.get(0).get('Id'));
            } else {
                pageState.put('firstId', records.get(0).get('Id'));
                pageState.put('lastId', records.get(records.size() - 1).get('Id'));
            }
        }
        return pageState;
    }

    private static List<String> describeSObjectFields(String sObjectName) {
        List<String> fields = new List<String>();
        SObject sObj = (SObject) Type.forName(sObjectName).newInstance();
        DescribeSObjectResult objResult = sObj.getSObjectType().getDescribe();
        Map<String, SObjectField> fieldsMap = objResult.fields.getMap();
        for (String field : fieldsMap.keySet()) {
            DescribeFieldResult fieldResult = fieldsMap.get(field).getDescribe();
            if (fieldResult.isAccessible() && fieldResult.getType() != Schema.DisplayType.BASE64) {
                fields.add(fieldResult.getName());
                if (fieldResult.getType() == Schema.DisplayType.REFERENCE) {
                    List<SObjectType> sObjectTypes = fieldResult.getReferenceTo();
                    SObjectType referenceTo = sObjectTypes.get(0);
                    String relName = fieldResult.getRelationshipName();
                    String nameField = getNameField(referenceTo);
                    fields.add(relName + '.' + nameField);
                }
            }
        }
        return fields;
    }

    private static String getNameField(SObjectType sObjType) {
        String fieldName = null;
        DescribeSObjectResult relObjResult = sObjType.getDescribe();
        Map<String, SObjectField> fieldsMap = relObjResult.fields.getMap();
        for (String field : fieldsMap.keySet()) {
            DescribeFieldResult fieldResult = fieldsMap.get(field).getDescribe();
            if (fieldResult.isNameField()) {
                fieldName = fieldResult.getName();
                break;
            }
        }
        return fieldName;
    }
}

If you need help with Salesforce or have something little more complex that might require some advanced developer knowledge, you can contact us at help@cloudanswers.com or chat with us on https://cloudanswers.com

• Apex
• Salesforce
• Pagination
• Programming
• Development