Dynamic query with optional where clauses using Slick 3

Question

I'm trying to implement a method to return filtered results, based on a set of parameters which may or may not be set. It doesn't seem like chaining multiple filters is possible conditionally, i.e. starting off with one filter...

val slickFlights = TableQuery[Flights]
val query = slickFlights.filter(_.departureLocation === params("departureLocation").toString)

Conditionally adding another filter to the query (if it exists in the Map of params) doesn't seem to work...

if (params.contains("arrivalLocation")) {
      query.filter(_.arrivalLocation === params("arrivalLocation").toString)
}

Can this sort of conditional filtering be done using Slick through other means?

I've come across the MaybeFilter: https://gist.github.com/cvogt/9193220, which seems to be a decent approach for handling exactly this. However it doesn't seem to work with Slick 3.x

Following Hüseyin's suggestions below, I have also tried the following:

def search(departureLocation: Option[String], arrivalLocation: Option[String]) = {
    val query = slickFlights.filter(flight =>
       departureLocation.map {
          param => param === flight.departureLocation
       })

Where slickFlights is a TableQuery object val slickFlights = TableQuery[Flights]. However this produces the following compilation error:

value === is not a member of String

Intellij also complains about the === being an unknown symbol. Doesn't work with == either.

Urey

Answer 1

For the benefit of anyone else trying to get optional filters working in Slick, have a look at the answer here: right usage of slick filter. I finally managed to get it working with the following:

def search(departureLocation: Option[String], arrivalLocation: Option[String]) = {
  val query = for {
    flight <- slickFlights.filter(f =>
       departureLocation.map(d => 
         f.departureLocation === d).getOrElse(slick.lifted.LiteralColumn(true)) && 
       arrivalLocation.map(a => 
         f.arrivalLocation === a).getOrElse(slick.lifted.LiteralColumn(true))
    )
  } yield flight

The key bit being the .getOrElse(slick.lifted.LiteralColumn(true)) on the end of the map, which causes Slick to render SQL as follows if for example only the departureLocation is set...

select * from `flight` 
where (`departureLocation` = 'JFK') and true

whereas without it the SQL looked like...

select * from `flight` 
where (`departureLocation` = 'JFK') and (`arrivalLocation` = '')

which obviously meant that it came back with no rows.

Urey

Thanks for this, still helpful to this day – Magnifico

Answer 2

A simpler approach without for comprehension:

import slick.lifted.LiteralColumn

val depLocOpt = Option[Long]
val slickFlights = TableQuery[Flights]
val query = slickFlights.filter { sf => 
  if (depLocOpt.isDefined) sf.departureLocation === depLocOpt.get
  else                     LiteralColumn(true)
}

UPDATE: you can shorten it more with fold:

val depLocOpt = Option[Long]
val slickFlights = TableQuery[Flights]
val query = slickFlights.filter { sf => 
  depLocOpt.fold(true.bind)(sf.departureLocation === _)
}

Adherence

Answer 3

January 2019

No more need to invent your own wheels!

At last Slick 3.3.0 includes the following helpers:

• filterOpt
• filterIf

So, for example:

case class User(id: Long, name: String, age: Int)
case class UserFilter(name: Option[String], age: Option[Int])

val users = TableQuery[UsersTable]

def findUsers(filter: UserFilter): Future[Seq[User]] = db run {
  users
    .filterOpt(filter.name){ case (table, name) =>
      table.name === name
    }
    .filterOpt(filter.age){ case (table, age) =>
      table.age === age
    }
    .result
}

Undamped

So simple now! Thanks! – Infamous

Answer 4

Ross Anthony's (excellent) answer can be further generalized by using foldLeft:

slickFlights.filter { f =>
    val pred1 = departureLocation.map(f.departureLocation === _)
    val pred2 = arrivalLocation.map(f.arrivalLocation === _)
    val preds = pred1 ++ pred2  //Iterable
    val combinedPred = preds.foldLeft(slick.lifted.LiteralColumn(true))(_ && _)
    combinedPred
}

This way when introduced with another optional constraint, it can simply be mapped (like pred1 and pred2) and added to the preds Iterable, the foldLeft will take care of the rest.

Ebbie

Answer 5

I'm using Slick 3.3.x. My solution is:

def search(departureLocation: Option[String], arrivalLocation: Option[String]) =
  slickFlights
    .filterOpt(departureLocation)(_.departureLocation === _)
    .filterOpt(arrivalLocation)(_.arrivalLocation === _)

Ascender

Answer 6

I just came up with a new soluion:

implicit class QueryExtender[E, U, C[_]](base: Query[E, U, C]) {
  def filterOption[T: BaseTypedType](option: Option[T], param: E => Rep[T]) = {
    option.fold {
      base
    } { t => base.filter(x => param(x) === valueToConstColumn(t)) }
  }
}

If you have a query (slickFlights) an option value and a selector, you can use it with slickFights.filterOption(departureLocation, _.departureLocation).

Midstream