Ross Anthony Ross Anthony - 3 days ago 5
Scala Question

Dynamic query with optional where clauses using Slick 3

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

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.

Comments