How to do pagination in apex

Use Case

Whenever we are building a data table in LWC we always need to ask one important questions. What will be the size of the data which we are displaying on UI. If data is large then we need to do pagination else we don’t need pagination. I usually follow this rule that if we are displaying a setup objects like custom setting or custom metadata we don’t need to pagination for most of the use cases, otherwise for all other objects(Account, case, event) we need to do pagination.

Frontend vs Backend Pagination

We can perform pagination on both LWC and apex. Lets say we have a dataset of 20,000 records and we are displaying 100 records per page. On apex side we are doing a simple query of

select id, name from Account order by Name

For these records if we do pagination on frontend then we will do an api call to apex to query 20,000 records and display first 100 records. Then if user click on next page we display next 100 records. Main problem with this approach will be first load time of data-table will be high. Also if customers say that right now we just have 20k records but within few years they might have more than 50k records and we will hit soql query limit(50K). So again for setup objects we can do pagination on frontend but for all other objects we should try to implement pagination in apex.

Where to Start

If we start looking for pagination on apex side first solution that we get is using Offset. We can write our pagination method like

This is easiest and most clean solution, but there is a limitation of Offset. We can use maximum offset value as 2000. So maximum records we can query are 2000.

Custom apex Pagination

Now we need to build a custom pagination solution. what we can do is add a where clause on order by field. Our pagination method will look like this

Above solution will work fine if account names(field on which your are using order by) are unique. But there can be records with same name. Lets take below example of Account names

Acc1, Acc2, Acc3….. Acc9, Acc10, Acc10, Acc11, Acc12 … (String sort will not work this way, but for simplicity lets assume that we get records in this order)

If we have page size of 10 then we will get records from Acc1…..Acc9,Acc10 in first list. Then when we ask for second page lastrecordName value will be Acc10 so our query will become

SELECT Id, Name from Account where name > ‘Acc10’ WITH SECURITY_ENFORCED order by Name limit :pageSize

With above query results we will get records in order Acc11,Acc12 …

So we missed a record(Acc10) in sorting. How to resolve this. we need to perform a sort on some unique field along with name field also. How will our pagination method will look like after change

In above method we are using order by both on Name and Id (We can use any unique field in place of Id like externalId.) field. Our where clause changes to add both Name and Id field. Above method will handle any records which have duplicate names and filter them based on Id sort.

There are still more enhancements that we can do. We can decide whether we want to do asc or desc ordering of records, Pass different field or object on which we want to do order by, Get next page or Previous Page(we can handle previous page by storing all the records in LWC also). So a Generic method structure for ordering of records will look like

public static List<Sobject> getRecords (String objectName, List<String> fieldsToQuery, String orderbyField, Id lastId, Integer pageSize, Boolean isAsc)

Make sure whenever we are using string variables direct in soql query without :(bind variable), we must sanitized them.

If you learned something new…. Clap 👏 🙂