Are you using RESTful services in APEX or ORDS? Did you know you can get free pagination and filtering when making collections or table rows available with APIs?
In this post, we'll learn how we can utilize features of ORDS to sort, filter, and paginate results. Redstone Content Solutions uses these features for this very blog, to determine which blog posts to show by keyword or draft status. The blog posts are written in an APEX app against a table with AutoREST enabled.
This post will use the example dataset EMP, which represents a list of employees. Let's pretend we have a resource at this location: https://example.com/ords/travis/emp/
First, we'll talk about ordering. We can use $orderby along with a column name and sorting options to determine how our results are sorted. This query would show all employees, ordered by salary ascending, then by employee name descending:
https://example.com/ords/travis/emp/?q={"$orderby":{"SALARY":"ASC","ENAME":"DESC"}}
We can also name where we'd like the null values to appear:
https://example.com/ords/travis/emp/?q={"$orderby":{"SALARY":["ASC","NULLS FIRST"]}}
This blog uses $orderby to sort blog posts by publication date, putting the most recent items at the top!
Next up, lets learn about the built-in filtering. Using AutoREST provides filters over the data, with no additional setup steps necessary. Let's look at this URL:
https://example.com/ords/travis/emp/?q={"ENAME":"JOHN"}
As you might expect, this will filter the returned data so the ENAME column must be equal to JOHN. There are a number of other comparators available. I've listed the most common ones below, with an example of how it might be used:
| Comparator | Used with | Example |
|---|---|---|
$eq | strings, numbers, dates | {"deptno":{"$eq":20}} |
$ne | strings, numbers, dates | {"deptno":{"$ne":20}} |
$lt and $lte | numbers, dates | {"salary":{"$lte":22000}} |
$gt and $gte | numbers, dates | {"salary":{"$gt":22000}} |
$like | strings | {"name":{"$like":"Tra%"}} |
$null and $notnull | nulls | {"direct_deposit":{"$null":null} |
You may also combine these values, to search for employees with names starting with “Tra” who work in the accounting department
q={"deptno":{"$ne":20}, "name":{"$like":"Tra%"}}
The query above is using an implicit $and. You may also explicitly use $and or $or in your query
q={"$and":[{"deptno":{"$ne":20}, {"name":{"$like":"Tra%"}]}
q={"$or":[{"name":{"$like":"Sara%"}, {"name":{"$like":"Tra%"}]}
Lastly, lets talk about pagination. Using pagination is like serving a great meal in courses rather than overwhelming diners with everything at once. By delivering data in smaller, manageable chunks, servers can respond more quickly and efficiently, while clients can process information without getting bogged down by massive data transfers. This approach saves bandwidth, improves performance, and creates a smoother experience for end users.
To use pagination, we simply need to include some url parameters to provide the number of items per page (limit) and how many pages deep we are (offset).
https://example.com/ords/travis/emp/?limit=50&offset=150
This example query would provide the next 50 rows of data, starting at row 151. With some simple math, you can navigate as deep as you'd like! Additionally, ORDS includes some helpful information to know how many pages to expect
We're able to do some trivial math and determine how many pages of content there are:
3064 total results / 50 per page = 62 pages
Additional Reading
There are lots of features included including other filtering methods, and quick ways to build API endpoints included in the ORDS Development Guide.
You can connect on LinkedIn with Redstone and with Travis directly.