
# 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

```dart
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 |

- A `Page` runs a second query, a count. For a `@Query` method returning a `Page`, give its count statement in `countQuery`.
- A `Slice` reads one more row than the page size to know whether another follows, and runs no count. Prefer it for "load more" lists over big tables.
- `Page.empty([pageable])` is an empty page.

## 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:

- sort properties are entity properties, written as their columns; for a `@Query`, identifiers, quoted (`a.b`);
- directions are only `asc` and `desc`;
- a `@Query` that returns an entity checks the name against the entity's properties and columns, and one that returns a class against that class's properties (`authorName` sorts by `author_name`) and the entity's;
- a property that does not exist is a `PropertyReferenceException` whose message writes the name escaped, so a name with a line break cannot forge a log line.

Two rules remain yours, because only you know them:

- **cap the size.** A client that sends `size=1000000000` gets the whole table. The web glue caps it at `spring.data.web.pageable.max-page-size` (see [In a web application](web.md));
- **allow-list the sort properties.** A sort is an `ORDER BY` oracle: a client can learn the order of values it cannot read, such as a password hash.

## In Spring (Java)

```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

- [Derived queries](derived-queries.md)
- [@Query](query.md)
- [In a web application](web.md)
