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

[scala]
// 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)
}
}
[/scala]

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

[scala]
// 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)
.query

query.list

}
[/scala]

Where did the if‘s go!

Liquid filtering

T

Over at agoravoting

We have a situation where we have to collectively choose among many options, this is scaling the solution space. It is infeasible to apply voting as is, because voters cannot consider all these options to make a judgement. So what we do is to distribute the cognitive load in a way that reflects user delegation. The problem of liquid filtering is the assignment of voters to questions according to delegation choices, in an optimal way.

Continue reading here