Oleh Tsivan , Scala / Java Developer
Implementing pagination in Scala projects using Slick is a very common task, but it’s not always clear how you can use Slick efficiently. It's obvious that requesting all records from a schema in one go is a bad idea no matter how many records are in that schema. But building efficient database requests is more than just getting a small number of values from a table.
Here are several aspects of implementing pagination on the server:
- The server should get only the necessary values, not all of them at once.
- The server should use an offset to get only the entries for a specific page.
- The query should return not only items, but also additional data to the client.
- The returned items should be filtered before they're sent to the client.
The third point needs clarification. What kind of additional data may the client application need? It's often necessary to return the total quantity of items, the availability of the next page, and other data depending on the front-end application needs.
We'll show a way to implement efficient pagination with Slick for Scala projects according to the requirements above.
Simple Pagination Example with Slick
We’ll start with a simple example of pagination with Slick. Let's say that our task is to get a list of comments that were published by a user. Your Scala application will have two entities — the user and the comment — with the one-to-many relation: a user can have many comments, and a single comment will belong to only one user.
Here’s what a
Comment entity might look like (we won't really need the
User entity to demonstrate how pagination
with Slick and Scala can be done):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
We can get the entire list of comments using the user ID provided by the client application:
1 2 3 4 5
This obvious implementation will strain the database layer too much (a user can have a thousand comments, and such request will get all of them), which is why we should use "paginated" select to make the database requests more efficient.
The rewritten method
findAll() (below) will accept not only the user ID, but also a limit — the number of
records to be rendered on a single page, and an offset — an integer value for the start record. Needless to
say that the limit and offset values must be provided by the client application.
Let's use the Slick methods
drop() for the offset and
take() for the limit:
1 2 3 4 5
findAll() method does exactly what we wanted: it gets only the necessary number of comments thus helping
us spare the bandwidth and performance of the hardware.
But, as we discussed in the introduction, that's not all we can do to make Slick pagination efficient. The next step is to modify the request in order to get more information about the records.
Getting More Information About the Records
When implementing server-side pagination, it’s often necessary to get additional data about the records. For example, you may need to know the total number of records so that the client application can show this number to the user. Additionally, it's good to know if there’s the next page, which will help you decide whether the client application should send a request for that next page.
To handle these additional data in a convenient way, you can create a model that will manage various data depending
on the requirements of your application. Our model
PaginatedResult will contain these three parameters: the list of
comments, the total amount of comments, and the availability of the next page:
1 2 3 4 5
We should rewrite
findAll() accordingly to return
1 2 3 4 5 6 7 8 9 10 11 12 13 14
As we can see from the code above, the
findAll() method gets the requested entities and their length
The code also calculates whether there's the next page.
Now that we've implemented pagination with Slick, one thing is left to discuss — how to sort the entities received from the database.
Sorting the Database Results With Slick
Sorting the database results is another task that we often need to implemented when working on server-side pagination. It's quite easy to change our pagination example to sort the returned data by a given parameter. Here’s the code that sorts comments by date:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
It's important to call the
sortBy() method before you
drop the records and
take the necessary amount of
elements. If you first call
take(), and then try to sort the filtered entities with
elements will be incorrectly selected from the unsorted collection and only after that sorted among themselves.
The simple approach we've described enables you to request, filter, and sort entities received from database and, as a result, implement efficient pagination using Slick in your Scala application.