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.
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
}