CODEWITHSUNDEEP
scalaslickscalaquery
bgvoka
bgvoka

Reputation: 65

Slick dynamic optional query or OR filter

I have an issue that I cannot solve for few days,

I'd like to make a dynamic query depending on my optional value. If a value is defined, I want to query for the selected results, otherwise to give me * projection of the table. As it is impossible to mix Lift Embedding DSL with normal Scala code in for-comprehension, is there any other way of achieveing this?

val x: Option[String] = Some("John")

something that would look like or do the thing 

val result = for {
   w <- workers if (x.isDefined) w.name === x else * projection 
}

Also, is there a possible way to write a function that has optional arguments and chain them in a query that will produce in WHERE clause OR condition? for dynamic number of OPTIONAL arguments, I've managed to do this with MaybeFilter but only for AND condition.

case class MaybeFilter[X, Y](val query: scala.slick.lifted.Query[X, Y, Seq])     {
  def filteredBy(op: Option[_])(f: (X) => Column[Option[Boolean]]) = {
    op map { o => MaybeFilter(query.filter(f)) } getOrElse { this }
  }
}

implicit def maybeFilterConversor[X, Y](q: Query[X, Y, Seq]) = new MaybeFilter(q)

val x: Option[String] = Some("Ana")
val y: Option[String] = Some("Lemic")

val result = for { r <- radnik.filteredBy(x)(_.ime === x).filteredBy(y)(_.prz === y).query } yield r

prints out select x2.`Mbr`, x2.`Ime`, x2.`Prz`, x2.`Sef`, x2.`Plt`, x2.`Pre`, x2.`God` from `radnik` x2 where (x2.`Ime` = 'Ana') and (x2.`Prz` = 'Lemic')

I need a function that will chain any number of optional conditions like the filteredBy but that will produce me an OR filter bind to it to achieve something like

select x2.`Mbr`, x2.`Ime`, x2.`Prz`, x2.`Sef`, x2.`Plt`, x2.`Pre`, x2.`God` from `radnik` x2 where (x2.`Ime` = 'Ana') or (x2.`Prz` = Lemic') or (x2.`God` >= '1950')

like filteredBy(someth).orFilteredBy(someth).filteredBy(someth)

Thanks in advance.

Upvotes: 3

Views: 1641

Answers (1)

Federico Baron
Federico Baron

Reputation: 997

As showed in official documentation, you can build dynamic filter criteria like this:

val x: Option[String] = Some("Ana")
val y: Option[String] = Some("Lemic")

val result = radnik.filter { r =>
  List(
      x.map(radnik.ime === _),
      y.map(radnik.prz === _)
  ).collect({case Some(criteria)  => criteria}).reduceLeftOption(_ || _).getOrElse(true: Rep[Boolean])
}

Upvotes: 0

Related Questions