Dynamic query parameters in Slick (sorting)

Question

I'm trying to convert anorm queries to slick in one of Play 2.3 samples, but I'm not sure how to implement dynamic sorting.

This is the original method:

def list(page: Int = 0, pageSize: Int = 10, orderBy: Int = 1, filter: String = "%"): Page[(Computer, Option[Company])] = {

    val offest = pageSize * page

    DB.withConnection { implicit connection =>

        val computers = SQL(
        """
          select * from computer 
          left join company on computer.company_id = company.id
          where computer.name like {filter}
          order by {orderBy} nulls last
          limit {pageSize} offset {offset}
        """
        ).on(
            'pageSize -> pageSize,
            'offset -> offest,
            'filter -> filter,
            'orderBy -> orderBy
        ).as(Computer.withCompany *)

        val totalRows = SQL(
        """
          select count(*) from computer 
          left join company on computer.company_id = company.id
          where computer.name like {filter}
        """
        ).on(
            'filter -> filter
        ).as(scalar[Long].single)

        Page(computers, page, offest, totalRows)

    }

}

So far I've got this far with the first query:

val computers_ = (for {
    (computer, company) <- Computer.where(_.name like filter) leftJoin
        Company on (_.companyId === _.id)
} yield (computer, company.?)).list

How do I do the "order by" part in slick, bearing in mind it's a column name passed to the method dynamically as a parameter?

Scala 2.10.4 / Play 2.3 / Slick 2.0.2

Table classes generated by Slick code generator below:

package tables
// AUTO-GENERATED Slick data model
/** Stand-alone Slick data model for immediate use */
object Tables extends {
  val profile = scala.slick.driver.H2Driver
} with Tables

/** Slick data model trait for extension, choice of backend or usage in the cake pattern. (Make sure to initialize this late.) */
trait Tables {
  val profile: scala.slick.driver.JdbcProfile
  import profile.simple._
  import scala.slick.model.ForeignKeyAction
  // NOTE: GetResult mappers for plain SQL are only generated for tables where Slick knows how to map the types of all columns.
  import scala.slick.jdbc.{GetResult => GR}

  /** DDL for all tables. Call .create to execute. */
  lazy val ddl = Company.ddl ++ Computer.ddl

  /** Entity class storing rows of table Company
   *  @param id Database column ID PrimaryKey
   *  @param name Database column NAME  */
  case class CompanyRow(id: Long, name: String)
  /** GetResult implicit for fetching CompanyRow objects using plain SQL queries */
  implicit def GetResultCompanyRow(implicit e0: GR[Long], e1: GR[String]): GR[CompanyRow] = GR{
    prs => import prs._
    CompanyRow.tupled((<<[Long], <<[String]))
  }
  /** Table description of table COMPANY. Objects of this class serve as prototypes for rows in queries. */
  class Company(tag: Tag) extends Table[CompanyRow](tag, "COMPANY") {
    def * = (id, name) <> (CompanyRow.tupled, CompanyRow.unapply)
    /** Maps whole row to an option. Useful for outer joins. */
    def ? = (id.?, name.?).shaped.<>({r=>import r._; _1.map(_=> CompanyRow.tupled((_1.get, _2.get)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))

    /** Database column ID PrimaryKey */
    val id: Column[Long] = column[Long]("ID", O.PrimaryKey)
    /** Database column NAME  */
    val name: Column[String] = column[String]("NAME")
  }
  /** Collection-like TableQuery object for table Company */
  lazy val Company = new TableQuery(tag => new Company(tag))

  /** Entity class storing rows of table Computer
   *  @param id Database column ID PrimaryKey
   *  @param name Database column NAME 
   *  @param introduced Database column INTRODUCED 
   *  @param discontinued Database column DISCONTINUED 
   *  @param companyId Database column COMPANY_ID  */
  case class ComputerRow(id: Long, name: String, introduced: Option[java.sql.Timestamp], discontinued: Option[java.sql.Timestamp], companyId: Option[Long])
  /** GetResult implicit for fetching ComputerRow objects using plain SQL queries */
  implicit def GetResultComputerRow(implicit e0: GR[Long], e1: GR[String], e2: GR[Option[java.sql.Timestamp]], e3: GR[Option[Long]]): GR[ComputerRow] = GR{
    prs => import prs._
    ComputerRow.tupled((<<[Long], <<[String], <<?[java.sql.Timestamp], <<?[java.sql.Timestamp], <<?[Long]))
  }
  /** Table description of table COMPUTER. Objects of this class serve as prototypes for rows in queries. */
  class Computer(tag: Tag) extends Table[ComputerRow](tag, "COMPUTER") {
    def * = (id, name, introduced, discontinued, companyId) <> (ComputerRow.tupled, ComputerRow.unapply)
    /** Maps whole row to an option. Useful for outer joins. */
    def ? = (id.?, name.?, introduced, discontinued, companyId).shaped.<>({r=>import r._; _1.map(_=> ComputerRow.tupled((_1.get, _2.get, _3, _4, _5)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))

    /** Database column ID PrimaryKey */
    val id: Column[Long] = column[Long]("ID", O.PrimaryKey)
    /** Database column NAME  */
    val name: Column[String] = column[String]("NAME")
    /** Database column INTRODUCED  */
    val introduced: Column[Option[java.sql.Timestamp]] = column[Option[java.sql.Timestamp]]("INTRODUCED")
    /** Database column DISCONTINUED  */
    val discontinued: Column[Option[java.sql.Timestamp]] = column[Option[java.sql.Timestamp]]("DISCONTINUED")
    /** Database column COMPANY_ID  */
    val companyId: Column[Option[Long]] = column[Option[Long]]("COMPANY_ID")

    /** Foreign key referencing Company (database name FK_COMPUTER_COMPANY_1) */
    lazy val companyFk = foreignKey("FK_COMPUTER_COMPANY_1", companyId, Company)(r => r.id, onUpdate=ForeignKeyAction.Restrict, onDelete=ForeignKeyAction.Restrict)
  }
  /** Collection-like TableQuery object for table Computer */
  lazy val Computer = new TableQuery(tag => new Computer(tag))
}

UPDATE - SOLUTION The final solution is in this question.

Answer 1

My first answer plugs in the sorting function at the right place, but quickly grows complicated because of Slick's complicated typing. You can avoid these typing issues by using Slick's query composition to modify the query directly based on the desired ordering.

def list(page: Int = 0, pageSize: Int = 10, orderBy: Int = 1, filter: String = "%") = {
   //..
   val q = for {
     (computer, company) <- Computer.where(_.name like filter) leftJoin
                            Company on (_.companyId === _.id)
   } yield (computer, company.?)

   val sortedQ = orderBy match {
     case 1 => q.sortBy(_._1.id)
     case 2 => q.sortBy(_._1.description)
     // Others
   }

   val pagedQ = sortedQ.drop(page * pageSize).take(pageSize)

   pagedQ.list
}

Answer 2

The difference between Slick and Anorm is that Slick's queries are checked by the Scala compiler. Implementing such a dynamic parameter takes a bit more effort in Slick, but you get type safety in return. It is made particularly cumbersome to do in this case since your query ordering is a join of multiple tables.

In general, it should look roughly like this:

def orderings(code: Int): ((Computer, Company)) => Column[_] = {
   code match {
      case 1 => _._1.id
      case 2 => _._1.description
      // Other orderings
   }
)

def list(page: Int = 0, pageSize: Int = 10, orderBy: Int = 1, filter: String = "%") = {
   //..
   val computers_ = (for {
     (computer, company) <- Computer.where(_.name like filter) leftJoin
                            Company on (_.companyId === _.id)
   } yield (computer, company.?))
   .sortBy(orderings(orderBy).nullsLast)
   .drop(page * pageSize)
   .take(pageSize)
   .list
   //..

}

The general idea to map the integers you receive to the Slick columns on which you want to sort is the answer to your question.

Answer 3

Not sure if this is the best idea in the world but you could technically use shapeless to help you get a numbered tuple element, this will obviously be at the cost of compile-time safety. First convert the Company case class into a tuple with Company.unapply and then use shapeless's at(N) method (note that it's a 0-based index). Here's what that would look like:

def list(page: Int = 0, pageSize: Int = 10, orderBy: Int = 1, filter: String = "%") = {
   //..
   val computers_ = (for {
     (computer, company) <- Computer.where(_.name like filter) leftJoin
                            Company on (_.companyId === _.id)
   } yield (computer, company.?))
   .sortBy(Company.unapply(_._1).get.at(orderBy-1).nullsLast)
   .drop(page * pageSize)
   .take(pageSize)
   .list
   //..
}

In order to do this you will need shapeless:

<dependency>
    <groupId>com.chuusai</groupId>
    <artifactId>shapeless_2.11</artifactId>
    <version>2.3.1</version>
</dependency>

...and the following import:

import shapeless.syntax.std.tuple._

Use this technique at your own risk.