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.
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 === _)
}
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
}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With