Change pagination to limit/offset in the database properly

[bitemyapp]

Change pagination to limit/offset in the database properly

[rpeszek]

I will start working on this.

[rpeszek]

Proposal for a somewhat hacky but simple workaround.

This approach only requires small changes to existing logic.
The current approach retrieves all data and can be summarized by this pseudo-code:

import qualified Yesod.Paginator as Page
  ...
  allRows <- runDB retrieveAllStuff
  dataPages <- Page.paginate 20 allRows
  let perPageRows = Page.pageItems (Page.pagesCurrent dataPages)
  colonnadeStuff = ... Page.simple 20 dataPages
  -- display #perPageRows and #colonnadeStuff pagination 

A simple workaround for this issue is to do something like this:

  rowCount <- runDB retrieveAllStuffCount
  dataPages <- Page.paginate 20 [0..(rowCount - 1)]
  let perPageRowNums = Page.pageItems (Page.pagesCurrent dataPages)
  perPageRows <- runDB retrieveStuffWithOffsetAndLimit (head perPageRowNums) 20
  colonnadeStuff = ... Page.simple 20 abstractPages
  -- display #perPageRows and #colonnade pagination 

Yesod.paginator provides selectPaginated that seems to be targeting Persistent backend. I can try to do a pull request against that project and suggest some more backend agnostic primitives.

But proposed approach should do in a pinch and maybe is not so bad.

[bitemyapp]

Is this closed by #117? I can't find an explicit limit/offset call for Esqueleto.

[bitemyapp]

Tested it, it does the right thing:

SELECT "abstracts"."id", "abstracts"."user", "abstracts"."author_title", "abstracts"."abstract_type", "abstracts"."author_abstract", "abstracts"."edited_title", "abstracts"."edited_abstract", "abstracts"."blocked", "abstracts"."is_draft", "users"."id", "users"."email", "users"."name", "users"."created_at", "users"."verified_at", "abstract_types"."id", "abstract_types"."conference", "abstract_types"."name", "abstract_types"."duration"
FROM "abstract_types" INNER JOIN "abstracts" ON "abstract_types"."id" = "abstracts"."abstract_type" INNER JOIN "users" ON "users"."id" = "abstracts"."user"
WHERE ("abstract_types"."conference" = ?) AND ("abstracts"."blocked" = ?)
 LIMIT 20 OFFSET 20; [PersistInt64 2,PersistBool False]