How to specify SLICK Query sortBy column from runtime parameter?

Question

I have the following SLICK query to get paged results for a data table with the name field matching some value criteria and sorted by the name column

val q = ThirdParties.where(_.name like criteria).sortBy(_.name.asc.nullsLast).drop(offset).take(pageSize)
val thirdParties = (for(s <-q) yield(s)).list map { case t: ThirdParty => t }

This works ok for me, but now I need to be able to pass a runtime parameter to the sortBy method that identifies the column on which to perform the sort by.
My method that calls the query will have an int that represents the index of the column in the data table.

How might I go about getting from an int column index to the necessary type required by the sortBy method?

Answer 1

You'll be losing some type safety by doing this, but maybe an approach like this would hurt the least:

This is with the coffee example from the Slick documentation. Let's assume you want a subset of your columns to addressed by 'index'. In our example, let's sat that we have for some reason, 2 price Int columns and also the sales column which we address as column 0, 1 or 2. . If you can put up with a minor infraction of DRY, such as this:

object Coffees extends Table[(String, Int, Double, Double, Int, Int)]("COFFEES") {
  def name = column[String]("COF_NAME", O.PrimaryKey)
  def supID = column[Int]("SUP_ID")
  def price1 = column[Double]("PRICE1")
  def price2 = column[Double]("PRICE2")
  def sales = column[Int]("SALES")
  def total = column[Int]("TOTAL")
  def * = name ~ supID ~ price1 ~ price2 ~ sales ~ total
  def nth = Vector(price1, price2, sales) // Your index-addressable columns 
}

Here Coffees.nth is a vector of columns, of both Int and Double.

scala> Coffees.nth
scala.collection.immutable.Vector[scala.slick.lifted.Column[_ >: Int with Double <: AnyVal]] = Vector(COFFEES.PRICE1, COFFEES.PRICE2, COFFEES.SALES)

Of course, picking the column to sort of at runtime implies that you have to deal with bogus column indexes - if you have only k columns and you ask for the k+1th column you either have to throw an exception, or silently pick a default column. That's a consequence of wanting to translate a dynamic input into what is usually static (and type safe).

If you're OK with an exception for a bogus column index, then (going back to your example)

 def q(colIndx: Int) = ThirdParties.where(_.name like criteria).
       sortBy(_.nth(colIndx).asc.nullsLast).
       drop(offset).take(pageSize)

Then to call the query

 val colIndx: Int = // gotten at runtime
 val thirdParties = (for(s <-q(colIndx)) yield(s)).list map { case t: ThirdParty => t }