Querying in Slick with many optional constraints

It’s a common use case to have to write queries with multiple constraints (ie where conditions) where each of these may or may not be present. For example, you could have an interface where the user may wish to filter according to different columns or criteria. In the old days this meant having to do very nasty sql generation by hand, by constructing some base query and then adding where clauses to it.

And even If you were not doing the sql by hand and using some kind of abstraction on top, the code would still require tedious and repetitive logic, polluting the code with if’s for each constraint that you may want to filter on.

One of the nice properties of Slick is how operations on queries are accumulated in the typical functional way. Queries are immutable, adding a constraint to a query returns another query that inherits all the operations done until then plus the new operation. This makes accumulation of constraints very readable, and makes queries composable and reusable. But how do we succinctly support the notion of accumulating optional operations?

Remember, this is Scala, this is the kind of thing we expect to be able to do concisely and elegantly, just like the Option type allows us to handle chaining of operations that may fail without having to write all those ugly if-else blocks. But unlike the case with Option, what we want to do is operate on a value that does exist, but where the operation itself is what is optional.

I’m convinced that there must be a well known functional pattern for this use case, but I don’t know what it is. Feel free to let me know in the coments, much appreciated. Anyhow, here’s what I came up with

// optionally filter on a column with a supplied predicate
case class MaybeFilter[X, Y](val query: scala.slick.lifted.Query[X, Y]) {
def filter[T](data: Option[T])(f: T => X => scala.slick.lifted.Column[Boolean]) = {
data.map(v => MaybeFilter(query.filter(f(v)))).getOrElse(this)

It looks more complicated than it is because of the type annotations to make it generic, but the mechanism is quite simple. If the optional constraint is present, return a new query with the filtering operation accumulated. Otherwise return the existing accumulated query unchanged.

And here’s how you use it, in this example there are five optional constraints

// example use case
def find(id: Option[Int], createdMin: Option[Date], createdMax: Option[Date], modifiedMin: Option[Date], modifiedMax: Option[Date]) = {

val query = MaybeFilter(Query(this))
.filter(id)(v => d => d.id === v)
.filter(createdMin)(v => d => d.created >= v)
.filter(createdMax)(v => d => d.created <= v)
.filter(modifiedMin)(v => d => d.modified >= v)
.filter(modifiedMax)(v => d => d.modified <= v)



Where did the if‘s go!

7 thoughts on “Querying in Slick with many optional constraints”

  1. Great example!
    But I don’t quite understand the signature f: T => X => scala.slick.lifted.Column[Boolean]. Does it mean a function with the parameter T is returning a function which takes a parameter X and this returns the Column? But how is d suddenly transformed into an Table and v is transformed into the actual type you want to check against?


  2. Great example!

    What I don’t understand is how the d finally gets transformed into a Table and the v into the value you check against by v => d => d.id === v?

    1. Thanks Anigni!

      The value is the result of mapping over the option[T] that you passed in, the line beginning with ‘data.map(v =>..’ in the implementation code. The table is supplied by the query object with which the MaybeFilter is initally constructed. In other words, the query object’s filter method expects a _function_ that _given_ a table returns a boolean condition. Hope that helps.

  3. I am trying to implement something using a similar kind of approach and l want to join multiple tables in this query .I add the MaybeFilter on an query that is to be applied to a table only if the param is not None. The issue is the table is getting appended to the query even when the param is None. Is there a way to prevent this ?
    Below is the sample query
    table1 d => condition1)
    table2 d => condition2)

Leave a Reply

Your email address will not be published. Required fields are marked *