For Everyone

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

7 min read
CloudAnswers photo
CloudAnswers
Share

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


CloudAnswers photo
CloudAnswers
Share

About CloudAnswers

Salesforce apps, powerful components, custom development, and consulting. Our experienced team helps you to create and modify workflow processes in salesforce.

Related Articles

For Everyone

AppExchange Review Series

As a long time Salesforce partner (since 2008), on both the consulting and AppExchange side, we realized we have a wealth of experience in building, installing, configuring, and using AppExchange apps. Our consulting customers often come to us with questions about which app would solve a particular problem, and as part of our AppExchange business we are often trying apps to learn what to do (and not to do) when building our own.

July 24, 2024

3 Min Read

For Everyone

Product Launch: CloudAnswers Shop Builder

Are you looking for an easy way to launch an ecommerce shop? Our new app, Shop Builder, is now in public beta! We’re looking for companies that want to build an online shop but don’t want to spend thousands building it out.

April 12, 2024

5 Min Read

For Everyone

A Day in the Life of a Project Manager at CloudAnswers

I'm Emily, and I've been a project manager at CloudAnswers for the last two years. It can be a nebulous role, but I like to say I act as a bridge between the product vision and tangible results, whether that is building a custom app for a client or one of our own Salesforce products. My typical day revolves around managing tasks, ensuring progress, and maintaining standards while adhering to project timelines.

March 22, 2024

5 Min Read