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.

Answer 1

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

Answer 2

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
}

Answer 3

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.