LIKE in where clause using filter in Slick 3.2.1 (Scala)

Question

I am still fairly new to slick and very much learning.

I an trying to create a search functionality that would be quite simple with plain SQL. However, I am hitting some obstacles when I am trying to do the same with Slick.

Trying to go by example from here: http://slick.lightbend.com/doc/3.2.1/queries.html#sorting-and-filtering i am starting to build a function as follows:

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

But this seem to be not right:

[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 =>

Also, IntelliJ complains about this:

I think I am misunderstanding something.

For a reference School definition related code:

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

And SchooSearchCriteria:

case class SchoolSearchCriteria(name: Option[String])

The criteria eventually going to be more complex than just a single field. I and just trying to figure out the mechanism for now.

Is it even the right direction to create search queries like that, given that the base query will eventually include more than a single table or even a single join?

Answer 1

So, I guess, I should answer my own question as well here, since it looks like I figured out the issue. Apparently what I was missing from the example here: http://slick.lightbend.com/doc/3.2.1/queries.html#sorting-and-filtering is the importance of collect part.

So eventually the way I've got it working is this:

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

However, I am not sure for a 100% how that works :) I hope this can help someone

Answer 2

This code compiles:

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

    db.run(q.result)
  }

---

  def search(schoolSearchCriteria: SchoolSearchCriteria, drop: Long = 0, take: Long = 100): Future[Seq[School]] =
    schoolSearchCriteria.name.map { n =>
      val q = schoolSearchBaseQuery(drop, take) filter { school =>
        school.name like s"%$n%"
      }
      db.run(q.result)
    }.getOrElse {
      Future.failed(new Exception("no name"))
//      Future.successful(Seq())
    }

---

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

    db.run(q1.result)
  }