How to do a paged query using ScalaQuery?

Question

I have a query that returns Person objects that I’m using to fetch one page of results from the database:

def page(pageNumber:Int, pageSize:Int) : Seq[Person] = database.withSession {
  val query = for(person <- People) yield person.mapped
  val startIndex = (pageNumber - 1) * pageSize
  query.list.slice(startIndex, startIndex + pageSize)
}

This works, but I want to know if I can do the paging in the database, in the same way as with the javax.persistence.Query API's setFirstResult and setMaxResults methods, instead of using slice on the resulting list.

Answer 1

The tests suggest using normal Scala collection methods like drop and take. Those will efficiently handle the JDBC ResultSet. It also looks like the appropriate LIMIT and OFFSET are added to the query.

Pipelines of operations on Scala collections may look like they're just filtering actual data, but as you can see here information can also travel back up the pipeline to make the computation as a whole more efficient.

Demonstration:

scala> import org.scalaquery.ql.extended.PostgresDriver.Implicit._
import org.scalaquery.ql.extended.PostgresDriver.Implicit._

scala> val q1 = Entities.map { e => e }.drop(10).take(10)
q1: org.scalaquery.ql.Query[models.Entities.type] = Query

scala> q1.selectStatement
res5: String = SELECT "t1"."guid","t1"."foo","t1"."bar" FROM "entities" "t1" LIMIT 10 OFFSET 10

Note that you need to import one of the specific driver implicits to make this work. See the end of the getting started guide for a list of driver names.