Filter ops with Slick 3.1

Leandro Bolívar
Pragmatic Scala
Published in
4 min readApr 12, 2016

I have been working with slick 3 for a while and the most recurring need I face is filtering queries with predicates that are defined by some optional values, these values often match the properties of the class that is used to map a slick table. For example, lets say I have a table of users:

The usual problem is that I ended up defining a repository function for every property I wanted to filter, ending up with methods like:

After doing some searching on the internet, I stumbled upon some proposals and used them as a base to come up with my own solution. The first step was to define some query ops trait that can add the functions I needed to the slick.lifted.Query class. I achieved this by defining an implicit class OptionFilter and adding some help from Typelevel Cats functional library:

As you noted, this trait needs an implementation of the HasDatabaseConfig trait which is defined in play-slick. From this part forward I will be assuming the simplest play-slick configuration for Play v2.4.

The first function, filteredBy, takes an optional value and maps it to the query that is being filtered with the function f. If that value is None, then it returns the unaltered query. The second function, foundBy, takes a list of operations (filter optional predicates), applies them to the table projection of the query, collects the Some values and reduces them according to the boolean operation defined by f. If all the operations defined in ops returned None, then the result will be an empty query. By default this method takes only the first match because I wanted to use it for finding single elements as the findBy function is often used. If you are new into the functional programming concepts, you can checkout what is exactly the Apply Typeclass in here.

Since we already defined the functions, now it is time to know how to use them. First we need to define a case class that is going to help us with the optional values that we want to pass to the find and filter functions. The UserRepository now looks like this:

I added the properties I wanted to use as filter parameters to the UserFilter case class, defined them as Option and defaulted them to None. Then I defined the find and list functions so that they take a UserFilter as the only parameter.

The find function applies to the users TableQuery the list of methods that depend on the value of the desired parameter that we want to filter the query by. If we want the method to apply the corresponding predicates to the underlying filter function, we just need to define the filter value with the desired parameter to Some. At the end, we will reduce those predicates with the OR method. You can eventually add any boolean function that you want in order to reduce the list. In the example I just wish to filter the query to the known unique values of the table since I know the method is defined to return a single element of the query collection.

The list function users filteredBy in cascade and applies the filter parameter in the same order that every function is called. In the example, I am filtering by three parameters just to keep it simple. As in the previous explanation, the only predicates that will have effect are the ones defined as Some.

If you want to find a user by its username or filter users by several values, here is how:

Time to point out the following:

  • I managed to get rid of all the boilerplate I got when defining a function for every parameter I wanted to use in order to filter a query, this was done in a simple and code clean way thanks to Typelevel Cats.
  • Implementing GET methods that take multiple query parameters in order to obtain an array of objects is simpler with the help of the filter case class since you don’t have to use for comprehensions to apply each filter to the resulting slick DBIOAction, it is executed on the query and it only takes one trip to the database.
  • Functional programming makes your life easier, kind of the whole point I am trying to make with these articles.
  • We are showing a simple use case, things get a bit uglier when a join to another table is needed so the resulting list function in the repository might not look as clean as the one above.

There’s still some things I want to show you about repository implementations because there are two other complications I have faced when coding them. The first one is about dealing with object-relational impedance (yes, that sounds fancy), it means when the user domain object does not perfectly match the UserRegister that I used to map the user table, in this example I assumed the UserRegister was my domain object. The second one is how to manage the operations of nested Scala monads (or, like in some cases, monad-like if you are a purist kind of programmer) that result from calling the repository’s functions (such as Future[Option[User]] or Future[List[User]]). I had to use consecutive map calls or nested for comprehensions for each Scala monad, it was some horrid code. Once again, thanks to Cats library, I was able to get pass this complications and do some interesting things that I will show you in upcoming posts. Any improvements on this example is most welcomed, happy coding!

--

--

Leandro Bolívar
Pragmatic Scala

The question isn't who is going to let me; it's who is going to stop me. Ayn Rand.