Slick中的动态查询参数(排序)

问题描述

我正在尝试在其中有一个Play 2.3示例，但我不确定如何实现动态排序.

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.

这是原始方法:

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

我该如何处理slick中的"order by"部分，同时牢记它是作为参数动态传递给方法的列名?

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

Scala 2.10.4 / Play 2.3 / Slick 2.0.2

下面的Slick代码生成器生成的表类:

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

更新-解决方案最终解决方案位于.

推荐答案

我的第一个答案在正确的位置插入了排序功能，但是由于Slick的复杂键入而很快变得复杂.您可以通过使用Slick的查询组合直接根据所需顺序修改查询来避免这些输入问题.

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
}

这篇关于Slick中的动态查询参数(排序)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！