postgresql - 在 Slick 3.2.1 (Scala) 中使用过滤器的 where 子句中的 LIKE

Question

我对 slick 还是很陌生，需要大量学习。

我试图创建一个使用普通 SQL 就非常简单的搜索功能。但是，当我尝试对 Slick 执行相同操作时遇到了一些障碍。

尝试从这里开始举例:http://slick.lightbend.com/doc/3.2.1/queries.html#sorting-and-filtering我开始构建如下函数:

private def schoolSearchBaseQuery(drop: Long, take: Long) = {
    (for {
      schools <- Schools.schools.filter(_.deletedAt.isEmpty)
    } yield schools).drop(drop).take(take)
  }

  def search(schoolSearchCriteria: SchoolSearchCriteria, drop: Long = 0, take: Long = 100): Future[Seq[School]] = {
    val q = schoolSearchBaseQuery(drop, take) filter { school =>
      List(
        schoolSearchCriteria.name.map(n => school.name like s"%$n%")
      )
    }

    db.run(q.result)
  }

但这似乎是不对的:

[error] /Users/ShurikAg/Dev/indago/indago-api/app/dao/SchoolDao.scala:97:47: inferred type arguments [List[Option[slick.lifted.Rep[Boolean]]]] do not conform to method filter's type parameter bounds [T <: slick.lifted.Rep[_]]
[error]     val q = schoolSearchBaseQuery(drop, take) filter { school =>
[error]                                               ^
[error] /Users/ShurikAg/Dev/indago/indago-api/app/dao/SchoolDao.scala:97:63: type mismatch;
[error]  found   : model.Schools => List[Option[slick.lifted.Rep[Boolean]]]
[error]  required: model.Schools => T
[error]     val q = schoolSearchBaseQuery(drop, take) filter { school =>

另外，IntelliJ 提示这个:

我想我误解了什么。

供引用学校定义相关代码:

package model

import driver.PGDriver.api._
import org.joda.time.DateTime
import play.api.libs.json._
import slick.lifted.Tag
import format.DateTimeFormat._
import model.media.Medias

case class School(id: Option[Int] = None,
                  addressId: Option[Int] = None,
                  name: String,
                  about: Option[String] = None,
                  numberOfStudents: Option[Int] = None,
                  websiteUrl: Option[String] = None,
                  mediaId: Option[Int] = None,
                  slug: String,
                  shortDescription: Option[String] = None,
                  ready: Boolean,
                  classrooms: Option[Int] = None,
                  yearEstablished: Option[String] = None,
                  displayCopyright: Boolean,
                  createdAt: DateTime = DateTime.now,
                  updatedAt: DateTime = DateTime.now,
                  deletedAt: Option[DateTime] = None,
                  createdBy: Option[String] = None,
                  updatedBy: Option[String] = None,
                  dliNumber: Option[String] = None)

object Schools {

  val schools = TableQuery[Schools]

  implicit lazy val schoolFormat: Format[School] = Json.format[School]

  Json.toJson[DateTime](DateTime.now)

}

class Schools(tag: Tag) extends Table[School](tag, "school") {

  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)

  def addressId = column[Option[Int]]("address_id")

  def name = column[String]("name", O.SqlType("character varying(255)"))

  def about = column[Option[String]]("about", O.SqlType("text"))

  def numberOfStudents = column[Option[Int]]("number_of_students")

  def websiteUrl = column[Option[String]]("website_url", O.SqlType("character varying(100)"))

  def mediaId = column[Option[Int]]("media_id")

  def slug = column[String]("slug", O.SqlType("character varying(255)"))

  def shortDescription = column[Option[String]]("short_description", O.SqlType("character varying(255)"))

  def ready = column[Boolean]("ready")

  def classrooms = column[Option[Int]]("classrooms")

  def yearEstablished = column[Option[String]]("year_established", O.SqlType("character varying(4)"))

  def displayCopyright = column[Boolean]("display_copyright")

  def createdAt = column[DateTime]("createdat")
  def updatedAt = column[DateTime]("updatedat")
  def deletedAt = column[Option[DateTime]]("deletedat")

  def createdBy = column[Option[String]]("createdby", O.SqlType("character varying(255)"))
  def updatedBy = column[Option[String]]("updatedby", O.SqlType("character varying(255)"))
  def dliNumber = column[Option[String]]("dli_number", O.SqlType("character varying(50)"))

  override def * =
    (
      id.?,
      addressId,
      name,
      about,
      numberOfStudents,
      websiteUrl,
      mediaId,
      slug,
      shortDescription,
      ready,
      classrooms,
      yearEstablished,
      displayCopyright,
      createdAt,
      updatedAt,
      deletedAt,
      createdBy,
      updatedBy,
      dliNumber
    ) <> (School.tupled, School.unapply)

  def addressIdUniqueIdx = index("school_address_id_uidx", addressId, unique = true)
  def application =
    foreignKey("school_address_id_fkey", addressId, Addresses.addresses)(
      _.id.?,
      onUpdate = ForeignKeyAction.Cascade,
      onDelete = ForeignKeyAction.Restrict
    )

  def mediaIdUniqueIdx = index("school_media_id_uidx", mediaId, unique = true)
  def logo =
    foreignKey("school_media_id_fkey", mediaId, Medias.medias)(
      _.id.?,
      onUpdate = ForeignKeyAction.Cascade,
      onDelete = ForeignKeyAction.Restrict
    )

  def slugUniqueIdx = index("school_slug_uidx", slug, unique = true)
}

和 SchooSearchCriteria:

case class SchoolSearchCriteria(name: Option[String])

标准最终会比单个字段更复杂。我现在只是想弄清楚这个机制。

考虑到基本查询最终将包含多个表甚至单个连接，创建这样的搜索查询是否是正确的方向？

Answer 1

所以，我想，我也应该在这里回答我自己的问题，因为看起来我已经解决了这个问题。显然我在这里的例子中遗漏了什么:http://slick.lightbend.com/doc/3.2.1/queries.html#sorting-and-filtering是collect部分的重要性。

所以最终我让它工作的方式是这样的:

def search(schoolSearchCriteria: SchoolSearchCriteria, drop: Long = 0, take: Long = 100): Future[Seq[School]] = {
    val q = schoolSearchBaseQuery(drop, take) filter { school =>
      List(
        schoolSearchCriteria.name.map(n => school.name like s"%${n.toLowerCase}%")
      ).collect({case Some(criteria)  => criteria}).reduceLeftOption(_ || _).getOrElse(true: Rep[Boolean])
    }

    db.run(q.result)
  }

但是，我 100% 不确定它是如何工作的:)我希望这可以帮助某人