Paging and sorting

A Pageable asks for one window of results (page number, page size, order); a Sort asks for an order alone. Give either as the last parameter of a derived query or a @Query method, or call findAllSorted and findAllPaged. A Page answer knows the total; a Slice only knows whether another page follows.

Why

A list screen or an API never loads a whole table. It loads 20 rows, in an order the user chose, and needs to know whether there are more. Writing LIMIT, OFFSET, ORDER BY and a count by hand for every finder is repetitive and easy to get wrong, and an order taken from a request is an injection risk when pasted into SQL. With Pageable and Sort the store writes all of that, and sort properties are checked against the entity.

Example

abstract interface class BookRepository
    implements
        ListCrudRepository<Book, int>,
        ListPagingAndSortingRepository<Book, int> {
  Future<Page<Book>> findByYearAfter(int year, Pageable pageable);
  Future<Slice<Book>> findByInStockIsTrue(Pageable pageable);
  Future<List<Book>> findByInStock(bool inStock, Sort sort);
}

// Page 0, 20 per page, newest first, then by title.
final request = PageRequest.of(
  0,
  20,
  Sort.byOrders([const SortOrder.desc('year'), const SortOrder.asc('title')]),
);

final page = await books.findByYearAfter(1950, request);
print('${page.numberOfElements} of ${page.totalElements}, page ${page.number + 1} of ${page.totalPages}');

if (page.hasNext) {
  final next = await books.findByYearAfter(1950, page.nextPageable());
}

final all = await books.findAllSorted(Sort.by(['title']));
final first = await books.findAllPaged(PageRequest.of(0, 10));

Pageable and PageRequest

Member Meaning
PageRequest.of(page, size, [sort]) Page page (from 0) of size items, ordered by sort.
Pageable.unpaged([sort]) Every result, maybe sorted.
pageNumber, pageSize, offset, sort The request (an unpaged request has no number, size or offset).
next(), previousOrFirst(), first(), withPage(n), hasPrevious Other pages.

PageRequest.of refuses a negative page, a size below one, and a page and size whose offset would pass 2^53 - 1: an InvalidPagingRequestException (an ArgumentError). So an offset never wraps around to another page. A Pageable of your own with a negative offset is refused by the stores.

Sort and SortOrder

Member Meaning
Sort.by(['title', 'year']) Ascending by each property, in order.
Sort.byOrders([SortOrder.desc('year'), ...]) Each order as given.
Sort.unsorted No order.
sort.and(other), descending(), ascending() Combine or flip.
SortOrder.asc(p), SortOrder.desc(p), SortOrder(p, direction:) One step.
order.ignoringCase() Compare in lower case.
order.withNullHandling(NullHandling.nullsLast) native (the store's choice), nullsFirst, nullsLast.
Direction.fromString('desc') asc or desc, case ignored.

A sort step is SortOrder here, Spring's Sort.Order: @Order orders beans.

Page and Slice

Member Slice Page
content, hasContent, numberOfElements yes yes
number, size, sort yes yes
isFirst, isLast, hasNext, hasPrevious yes yes
nextPageable(), previousPageable() yes yes
map((item) => ...) yes yes
totalElements, totalPages no yes

The order of the results

The order is the name's OrderBy first, then the Sort or the Pageable's sort. Without any order, the store's order is not guaranteed: page through a table only with a sort.

Input from requests

A Sort or Pageable built from request parameters never reaches the SQL as text:

Two rules remain yours, because only you know them:

In Spring (Java)

Page<Book> findByYearAfter(int year, Pageable pageable);

PageRequest.of(0, 20, Sort.by(Sort.Order.desc("year"), Sort.Order.asc("title")));
repository.findAll(Sort.by("title"));

The names are Spring Data's, except SortOrder (Spring's Sort.Order), findAllSorted and findAllPaged (Spring's overloaded findAll), and Sort.by taking a list.

See also