Generic pagination based on `seek`

[oyvindberg]

Builds on seek-support added in #100

Pagination with ClientCursor

On top of seek I managed to write a completely generic pagination mechanism. It works for any query, including when you join many tables together. It also works with most SQL expressions, as long as they can be compared in PG. There is a new DSL for this, built on top of the normal one. It's not built directly in, because JSON is used, and the SQL DSL shouldn't have a dependency on a JSON library.

It looks like this:

    // import a facade for the pagination DSL for your chosen JSON library
    import PaginationQueryZioJson.*

    businessentityRepo.select
      .where(_.businessentityid < rows.last.businessentityid)
      // first ordering
      .seekPaginationOn(_.modifieddate.desc)
      // add a second ordering. supports any sql expression that can be sorted
      .andOn(x => (x.businessentityid.underlying - 2).asc)
      // `continueFrom` is where you plug in a cursor you got from the client to continue
      .toChunk(limit = limit, continueFrom = None)
      .tap { case (_, maybeClientCursor) =>
        ZIO.succeed(println(maybeClientCursor.map(clientCursor => JsonEncoder[ClientCursor[Json]].encodeJson(clientCursor))))
      }

prints:

Some({"businessentity1.modifieddate":"2020-12-29T00:00:00","(businessentity1.businessentityid - 2::INTEGER)":1})

Uses seek

In the implementation you can see that it's built on top of seek:

    continueFrom match {
      case None =>
        val newQuery = initialCursor.part2s
          .foldLeft(query) { case (q, part2: ServerCursor.Part2[Fields, Row, t, n, E]) =>
            q.orderBy(part2.part1.v)
          }
          .limit(limit)
        Right((newQuery, initialCursor))

      case Some(clientCursor) =>
        initialCursor.withTupleFrom(clientCursor).map { cursor =>
          val newQuery = cursor.part3s
            .foldLeft(query) { case (q, part3: ServerCursor.Part3[Fields, Row, _, _, E]) =>
              q.seek(part3.part2.part1.v)(part3.value)
            }
            .limit(limit)
          (newQuery, cursor)
        }
    }

Properties

I'll copy in the documentation for ClientCursor to describe the properties of the cursor:

/** This will typically be JSON encoded and passed to clients.
  *
  * It represents a cursor that can be used to fetch the next page of results.
  *
  * The position is a given row for a set of [[order by]] expressions.
  *
  * The [[ClientCursor]] itself is a glorified [[Map]], with pairs of stringified `order by` expressions and a JSON representation of the corresponding value from the current row
  *
  * The design has a few interesting tradeoffs:
  *   - it leaks database column names to the client, so you may want to obfuscate/encrypt it
  *   - it can be re-used for "similar" queries, not just the exact same query. Fewer `order by` expressions or different ordering of `order by` expressions is fine.
  *   - [[SortOrderRepr]] does not encode ascending/descending or nulls first/last, but you're still anchored to a specific row for a set of orderings. If you want, you can change your query to go
  *     both ways from a given cursor.
  */
case class ClientCursor[E](parts: Map[SortOrderRepr, E]) 

todo:

• [ ] port pagination to anorm
• [ ] port pagination to doobie
• [ ] port pagination to play-json
• [ ] port pagination to circe
• [ ] documentation for both
• [ ] blog post