Slick 3: How to construct dynamic filter with optional column?

Question

I need to construct dynamic filter based on some criteria which involves optional column:

Criteria

case class Criteria(
  name: Option[String] = None,
  description: Option[String] = None,
)

Table mapping

class OrganizationsTable(tag: Tag) extends Table[OrganizationModel](tag, "organizations") {
    def id = column[Long]("id", O.PrimaryKey)
    def name = column[String]("name")
    def description: Rep[Option[String]] = column[Option[String]]("description")

    def * = (id, name, description) <> ((OrganizationModel.apply _).tupled, OrganizationModel.unapply)
  }

Filter

organizations.filter { model =>      

  List(
    criteria.name.map(model.name.like(_)),
    criteria.description.map(v => model.description.isDefined && model.description.like(v))
  )
  .collect({case Some(cr)  => cr})
   // value && is not a member of slick.lifted.Rep[_1]
  .reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
}

But I'm getting compilation error:

value && is not a member of slick.lifted.Rep[1] [error]
.reduceLeftOption( && _).getOrElse(true: Rep[Boolean])

If I add get to model.description like this:

criteria.description.map(v => model.description.isDefined && model.description.get.like(v))

it compiles fine, but throws runtime exception:

slick.SlickException: Caught exception while computing default value for Rep[Option[_]].getOrElse -- This cannot be done lazily when the value is needed on the database side

How to build dynamic query in slick which involves optional column?

Gabriele Petronella Gabriele Petronella · Accepted Answer · 2018-02-18T15:12:49

This is a utility I use in a project at work:

implicit class OptionFilter[E, U, C[_]](query: Query[E, U, C]) {
  import slick.lifted.CanBeQueryCondition
  def optionalFilter[O, T <: Rep[_]](op: Option[O])(f:(E, O) => T)(
    implicit wt: CanBeQueryCondition[T]): Query[E, U, C] =
      op.map(o => query.withFilter(f(_,o))).getOrElse(query)
}

Usage

criteria.optionalFilter(model.description)(_.description.? like _)

Essentially it applies the filter to a query if the target value (e.g. model.description) is defined, otherwise it just returns the original query.

Slick 3: How to construct dynamic filter with optional column?

1 Answers