Have you ever been frustrated by the 2,000 row limit when using the SOQL OFFSET keyword? You may have seen the QueryMore operation in the Salesforce SOAP API but became brutally disappointed when you found out there was no equivalent accessible from Apex. This article will examine an alternate technique you can use to get around this limit for some use cases. In fact, we will be building a near functional equivalent of the QueryMore operation in Apex using some fancy tricks!
Example
I created an example application to demonstrate the QueryMore functionality discussed in this article. The application consists of a Lightning Web Components data table with infinite loading/scrolling capabilities. It also contains functionality to create and delete test data.
GitHub URL: https://github.com/DNLZ/Apex-QueryMore-Example
Note: I sort of lied when I said the application is using infinite scrolling. This is because I couldn’t get the out-of-the-box infinite loading functionality to work in the LWC <lightning-datatable> component, due to what I suspect are bugs with the framework. As a work-around I am using a “Load More” button. I’ll update this blog post at a later time with more information on the issues I faced.
A Tale of Two Cases
To explain how the QueryMore functionality is built in Apex, we will be exploring two cases.
Case #1: the data being queried is sorted by record Id
Case #2: the queried data is sorted by a single arbitrary field (other then Id)
We will build on Case #1 in order to implement Case #2.
In the example application there is a button that toggles whether column sorting is enabled or disabled. When column sorting is disabled, the rows in the table are sorted by Id.
Case #1
Examine the following SOQL query which retrieves 2,000 Account records sorted by Id.
SELECT Name, NumberOfEmployees FROM Account ORDER BY Id LIMIT 2000
How can we now query the next 500 records like we would with the SOAP API’s QueryMore function? Well let’s take the Id of the last row returned by the previous query, and store it in a variable called idOfLastRow.
SELECT Name, NumberOfEmployees FROM Account WHERE Id > :idOfLastRow ORDER BY Id LIMIT 500
This query will get us the next chunk of 500 records. To get the next additional chunk, simply repeat the above query but using the new Id of the last row returned.
In the example application, this technique is how we are populating the data table, initially and when loading additional data, when the column sorting is turned off.
/**
* Gets the first 50 Account records in the DB sorted by Id
*/
private static List<Account> queryTestDataSortedById() {
return [
SELECT Name, NumberOfEmployees
FROM Account
ORDER BY Id
LIMIT 50
];
}
/**
* Gets up to an additional 25 Accounts from the db sorted by Id.
*
* @param lastId The Id of the last row in the current dataset.
* Used to determine the starting row offset of the returned dataset.
*/
private static List<Account> queryMoreTestDataSortedById(Id lastId) {
return [
SELECT Name, NumberOfEmployees
FROM Account
WHERE Id > :lastId
ORDER BY Id LIMIT 25
];
}
Note: The example application’s code is using a limit of 50 records for the initial dataset and 25 records when querying for additional data. These numbers are arbitrary and can be changed as you see fit. I chose smaller numbers to make the example application run smoother.
Case #2
But what if we want to sort by something other than Id? Say by Account name? We can do that but we still need to have Id as the last field in the ORDER BY clause.
SELECT Name, NumberOfEmployees FROM Account ORDER BY Name ASC, Id LIMIT 2000
Adding Id to the end of the ORDER BY clause should have no adverse impact on what you are doing, but it is needed to be able to query the next chunk.
To query the next batch of records, we need to use the Id and the Name in the last row from the previous query. We call them idFromLastRow and sortByFieldValueFromLastRow respectively.
SELECT Name, NumberOfEmployees
FROM Account
WHERE
Name > :sortByFieldValueFromLastRow ||
(Name = :sortByFieldValueFromLastRow AND Id > :idFromLastRow)
ORDER BY Name, Id
LIMIT 500
To illustrate why this works, lets imagine we ran the query over the following table of Accounts where sortByFieldValueFromLastRow is ‘Acme Supplies #3’ and idFromLastRow is 320.
| Id | Name |
| 700 | Acme Supplies #1 |
| 600 | Acme Supplies #2 |
| 310 | Acme Supplies #3 |
| 320 | Acme Supplies #3 |
| 330 | Acme Supplies #3 |
| 340 | Acme Supplies #3 |
| 200 | Acme Supplies #4 |
| 100 | Acme Supplies #5 |
The rows highlighted in red are those that met the (Name = :sortByFieldValueFromLastRow AND Id > :idFromLastRow) condition and the rows highlighted in yellow are those that met the Name > :sortByFieldValueFromLastRow condition. The rows highlighted in blue are the ones that met neither condition, which is what we would expected since we only wanted the rows that come later.
This is the same procedure used in the example application:
/**
* Gets up to 50 Account records sorted by a specified field and direction, from the db
*
* @param sortedBy The API name of the field to sort by
* @param sortedDirection Can be "asc" or "desc".
*/
private static List<Account> queryTestDataSortedByAdditionalField(String sortedBy, String sortedDirection) {
String queryString =
'SELECT Name, NumberOfEmployees '
+ 'FROM Account '
+ 'ORDER BY ' + sortedBy + ' ' + sortedDirection + ', Id '
+ 'LIMIT 50';
return Database.query(queryString);
}
/**
* Gets an additional 25 sorted Account records from the db.
* The lastId and lastValueOfSortedField parameters are used to determine the starting row offset of the returned dataset.
*
* @param sortedBy The API name of the field to sort by.
* @param sortedDirection Can be "asc" or "desc".
* @param lastId The Id of the last row in the current dataset.
* @param lastValueOfSortedField The value of the sortBy field in the last row of the current dataset.
* @param sortedFieldIsInteger Set to True if the sortBy field holds an integer, otherwise set to False.
* This parameter is needed to work around an issue where the LWC sends us an
* integer but Apex thinks it's a decimal
*/
private static List<Account> queryMoreTestDataSortedByAdditionalField(String sortedBy, String sortedDirection, Id lastId, Object lastValueOfSortedField, Boolean sortedFieldIsInteger) {
String directionOperator = sortedDirection == 'asc' ? '>' : '<';
// This hack is needed to avoid an issue where
// integers sometimes come through as Decimal types
lastValueOfSortedField = sortedFieldIsInteger ? Integer.valueOf(lastValueOfSortedField) : lastValueOfSortedField;
String queryString =
'SELECT Name, NumberOfEmployees '
+ 'FROM Account '
+ 'WHERE ' + sortedBy + ' ' + directionOperator + ' :lastValueOfSortedField '
+ 'OR (' + sortedBy + ' = :lastValueOfSortedField AND Id > :lastId) '
+ 'ORDER BY ' + sortedBy + ' ' + sortedDirection + ', Id LIMIT 25';
return Database.query(queryString);
}
Notice the QueryMore functionality can handle sorting the column in both ascending and descending directions as shown by the directionOperator variable being set on line 99 in the code snippet above.
Limitations
- The QueryMore functionality built out in this article can only support sorting on a single field. Generalizing the approach to support more fields in the
ORDER BYclause may be possible (in fact, I am pretty sure it is), but is out of the scope of this article. - The QueryMore functionality built out here does not work if the field being sorted contains null values. I believe we can get around this by adding the
NULLS LASTkeyword to the sort field in theORDER BYclause, but I have not confirmed this.
Other Notes
- This functionality is not just limited to the infinite scroll table use-case given in the example. For example, it can be used to transfer large amounts of data to an API.
- Could we have just called the SOAP API’s QueryMore() operation from Apex instead of doing all of this? Calling the SOAP API from Apex can be very cumbersome. The approach outlined here should be more convenient and efficient in most situations.
I included this post to my favorites