REST API: Sorting, Filtering, and Pagination

Web applications often have tables of data, whether it's the list of items for sale on Amazon, or notes in Evernote, and so on. Usually, users of the application are going to want to filter the results or sort through that data in some way.

If the dataset is pretty small, maybe a few hundred results, the API can return all the data at once and the front end will handle all the filtering, and no more API calls are required. Most of the time, however, the data could consist of tens of thousands to millions of rows, and it's better to just get the data you need from smaller API calls as opposed to trying to request a million results every time the page loads.

Recently, I made a backend API for some list endpoints, and implemented filtering, sorting, and pagination. There's not really a set standard for creating these types of endpoints, and almost every one I've come across is different in some way. I made a few notes on what made sense to me, so this resource could be helpful for someone who is working on designing an API.

Goals

In this article I'll make an example API endpoint and SQL query for various sort, paginate, and filter APIs, with users as the table for all examples.

Contents

• Response
• Pagination
• Sorting
  • Ascending vs. Descending
  • Single column
  • Multiple columns
• Filtering
  • String (exact)
  • String (exact, multiple)
  • String (partial)
  • Number (exact)
  • Number (greater than)
  • Number (less than)
  • Number (range)
  • Date (range)

Response

When using any pagination or filtering in an API, you're going to want to know how many results you have, how many results there are total, and what page you're on.

{
  content: [], // all the response items will go in this array
  page: 1, // current page
  results_per_page: 5, // how many items available in "content"
  total_results: 100 // total number of items
}

From there, you can discern that there are 20 pages with total_results / results_per_page and anything else you might need for the front end.

Pagination

Pagination is how you move between the pages when you don't want to retrieve all the results at once.

• Page and results per page are required inputs
• For the SQL query, offset is equal to (page - 1) * results_per_page

GET /users?page=3&results_per_page=20

SELECT * FROM users
LIMIT 20
OFFSET 40

Sorting

Sorting allows you to order the results by any field, in ascending or descending order.

Ascending vs. Descending

I always forget what ascending and descending mean for alphabetical, numerical, and date-based responses, so I wrote this up for reference.

Single column

If you only need to sort one column at a time, you could put the column name in sort_by and the sort direction in order.

GET /users?sort_by=first_name&order=asc

SELECT * FROM users
ORDER BY first_name ASC

Multiple columns

If the ability to sort multiple columns is required, you could comma-separate each column:order pair and put it in one sort parameter. This could also be used for a single column if you prefer the syntax.

GET /users?sort=first_name:asc,age:desc

SELECT * FROM users
ORDER BY first_name ASC, age DESC

Filtering

Filtering is by far the most complex of the three. There are several ways to handle it. Some APIs will use a POST and pass all the data in the body of the request for searching. This might be necessary for advanced searching in some situations, but a GET is preferable.

Some API will attempt to put everything on a single filter parameter, like this:

GET users?filter={"first_name":["Tania","Joe"],"age":[30,31,32]}

However, this will have to be URI encoded.

I've opted for treating each parameter as a column in the database.

String (exact)

Exact search by a single column.

GET /users?first_name=Tania

SELECT * FROM users
WHERE first_name = 'Tania'

String (exact, multiple)

Depending on how you want to handle the API, multiple options for a single column can be handled in different ways. If splitting by comma isn't an issue, it might be the easiest. You might also just want to repeat the parameter name or use a custom delimiter.

GET /users?first_name=Tania,Joe
GET /users?first_name=Tania&first_name=Joe
GET /users?first_name[]=Tania&first_name[]=Joe

SELECT * FROM users
WHERE first_name IN ('Tania', 'Joe')

Some systems might require using [] for multiple parameters of the same name, and some might now allow [], so I provided both options.

String (partial)

Often, searches are expected to be partial, so that when I look for "Tan" it will show me "Tania" and "Tanner". The solution I liked was using like:Tan as value as opposed to modifying the parameter (such as first_name[like]=Tan).

GET /users?first_name=like:Tan

SELECT * FROM users
WHERE first_name LIKE '%Tan%'

Number (exact)

Exact number search on a column.

GET /users?age=30

SELECT * FROM users
WHERE age = 31

Number (greater than)

Similar to like:, you can use gt: to handle greater than. Adding the option for gte: (greater than or equal) is also an option.

GET /users?age=gt:21

SELECT * FROM users
WHERE age > 21

Number (less than)

Same with lt: for less than lte: for less than or equal.

GET /users?age=lt:21

SELECT * FROM users
WHERE age < 21

Number (range)

If you need a range between two number values, using [and] in between them could be one option. This one could get complicated, depending on if you want to allow both greater than and greater than or equal, or other options.

GET /users?age=gt:12[and]lt:20

SELECT * FROM users
WHERE age > 12 AND age < 20

Date (range)

If you need a range between two dates, you can use start and end, or since and to.

GET /users?start=01-01-1970&end=09-09-2020

SELECT * FROM users
WHERE created_at BETWEEN '01-01-1970' AND '09-09-2020'

Conclusion

These examples are pretty simple and cover basic use cases. If your API is very complicated, you might need to change it up to add more options, particularly with ranges, and various combinations of "and" and "or". Hopefully this will be a helpful starting point!