Database.Esqueleto.Pagination

This module provides efficient pagination over your database queries. No OFFSET here - we use ranges to do this right!

The ideal "range" column for a datatype has a few properties:

1. It should have an index. An index on the column will dramatically improve performance on pagination.
2. It should be monotonic - that is, we shouldn't be able to insert new data into the middle of a range. An example would be a created_at timestamp field, or a auto-incrementing primary key.

This module offers two ways to page through a database. You can use the streamingEntities to get a ConduitT of Entity record values streaming out. Or, if you'd like finer control, you can use getPage to get the first page of data, and then nextPage to get the next possible page of data.

Documentation

data Page record typ

A Page record typ describes a list of records and enough information necessary to acquire the next page of records, if possible.

This is a distinct type from the Page in Database.Persist.Pagination because the pageFilters field needs a different type. As a result, some of this stuff is duplicated. It's possible that this can be fixed and more code could be shared.

Since: 0.1.1.0

Constructors

Page

Fields

• pageRecords :: [Entity record]

  The collection of records.

  Since: 0.1.1.0

• pageRecordCount :: Int

  The count of records in the collection. If this number is less than the pageSize field, then a call to nextPage will result in Nothing.

  Since: 0.1.1.0

• pageRange :: Range typ

  The minimum and maximum value of typ in the list.

  Since: 0.1.1.0

• pageDesiredRange :: DesiredRange typ

  The desired range in the next page of values. When the pageSortOrder is Ascending, then the rangeMin value will increase with each page until the set of data is complete. Likewise, when the pageSortOrder is Descending, then the rangeMax will decrease until the final page is reached.

  Since: 0.1.1.0

• pageField :: EntityField record typ

  The field to sort on. This field should have an index on it, and ideally, the field should be monotonic - that is, you can only insert values at either extreme end of the range. A created_at timestamp or autogenerated ID work great for this. Non-monotonic keys can work too, but you may miss records that are inserted during a traversal.

  Since: 0.1.1.0

• pageFilters :: SqlExpr (Entity record) -> SqlExpr (Value Bool)

  The extra filters that are placed on the query.

  Since: 0.1.1.0

• pageSize :: PageSize

  The desired size of the Page for successive results.

• pageSortOrder :: SortOrder

  Whether to sort on the pageField in Ascending or Descending order. The choice you make here determines how the pageDesiredRange changes with each page.

  Since: 0.1.1.0

streamEntities

Arguments

:: forall record backend typ m a. (PersistRecordBackend record backend, PersistQueryRead backend, PersistUniqueRead backend, BackendCompatible SqlBackend backend, BackendCompatible SqlBackend (BaseBackend backend), Ord typ, PersistField typ, MonadIO m)

=> (SqlExpr (Entity record) -> SqlExpr (Value Bool))

The filters to apply.

-> EntityField record typ

The field to sort on. This field should have an index on it, and ideally, the field should be monotonic - that is, you can only insert values at either extreme end of the range. A created_at timestamp or autoincremented ID work great for this. Non-monotonic keys can work too, but you may miss records that are inserted during a traversal.

-> PageSize

How many records in a page

-> SortOrder

Ascending or descending

-> DesiredRange typ

The desired range. Provide Range Nothing Nothing if you want everything in the database.

-> ConduitT a (Entity record) (ReaderT backend m) ()

Stream entities out of the database, only pulling a limited amount into memory at a time.

You should use this instead of selectSource because selectSource doesn't really work. It doesn't work at all in MySQL, and it's somewhat sketchy with PostgreSQL and SQLite. This function is guaranteed to use only as much memory as a single page, and if you tune the page size right, you'll get efficient queries from the database.

There's an open issue for selectSource not working: GitHub Issue.

Since: 0.1.1.0

rangeToFilters :: (PersistField typ, PersistEntity record) => Range (Maybe typ) -> EntityField record typ -> SqlExpr (Entity record) -> SqlQuery ()

Convert a DesiredRange typ into a SqlQuery that operates on the range. The DesiredRange is treated as an exclusive range.

Since: 0.1.1.0

getPage

Arguments

:: forall record backend typ m. (PersistRecordBackend record backend, PersistQueryRead backend, PersistUniqueRead backend, BackendCompatible SqlBackend backend, BackendCompatible SqlBackend (BaseBackend backend), Ord typ, PersistField typ, MonadIO m)

=> (SqlExpr (Entity record) -> SqlExpr (Value Bool))

The filters to apply.

-> EntityField record typ

The field to sort on. This field should have an index on it, and ideally, the field should be monotonic - that is, you can only insert values at either extreme end of the range. A created_at timestamp or autogenerated ID work great for this. Non-monotonic keys can work too, but you may miss records that are inserted during a traversal.

-> PageSize

How many records in a page

-> SortOrder

Ascending or descending

-> DesiredRange typ

The desired range. Provide Range Nothing Nothing if you want everything in the database.

-> ReaderT backend m (Maybe (Page record typ))

Get the first Page according to the given criteria. This returns a Maybe Page, because there may not actually be any records that correspond to the query you issue. You can call pageRecords on the result object to get the row of records for this page, and you can call nextPage with the Page object to get the next page, if one exists.

This function gives you lower level control over pagination than the streamEntities function.

Since: 0.1.1.0

nextPage :: (PersistRecordBackend record backend, PersistQueryRead backend, PersistUniqueRead backend, BackendCompatible SqlBackend backend, BackendCompatible SqlBackend (BaseBackend backend), Ord typ, PersistField typ, MonadIO m) => Page record typ -> ReaderT backend m (Maybe (Page record typ))

Retrieve the next Page of data, if possible.

Since: 0.1.1.0

emptyQuery :: SqlExpr (Entity record) -> SqlExpr (Value Bool)

An empty query value to pass to the functions when you don't have any filters to run.

Since: 0.1.1.0

module Database.Persist.Pagination.Types