scala - Slick:带左连接的查询中的动态 sortBy

Question

这是一个源自another question的问题.我需要能够动态传递要在具有左连接的 Slick 查询中进行排序的列。这种特殊情况下的问题是左连接表变得可选，我不知道如何处理。如果我使表 Company 不是可选的，我将得到 SlickException: Read NULL value for ResultSet column Path

例子:

def list(filter: String, orderBy: Int) = {

    DB.withDynSession {

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

        val sortedData = orderBy match {
            case 2 => data.sortBy(_._1.name) //Works ok, column from a primary table
            case 3 => data.sortBy(_._2.name) //Error "Cannot resolve symbol name", because table is optional
        }

    }

}

上面示例中使用的 Slick 自动生成的表类:

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

Answer 1

.?使用 <> 实现这会阻止您以后访问成员。因此，您需要在执行 .? 之前应用排序

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

    val sortedData = orderBy match {
        case 2 => data.sortBy(_._1.name) //Works ok, column from a primary table
        case 3 => data.sortBy(_._2.name) //Error "Cannot resolve symbol name", because table is optional
    }

    val optionalJoinData = sortedData.map{
      case (computer, company) => (computer, company.?)
    } // <- do .? last