Slick: how to implement find by example i.e. findByExample generically?

Question

I'm exploring the different possibilities on how to implement a generic DAO using the latest Slick 3.1.1 to boost productivity and yes there is need for it because basing the service layer of my Play Web application on TableQuery alone leads to a lot of boilerplate code. One of the methods I'd like to feature in my generic DAO implementation is the findByExample, possible in JPA with the help of the Criteria API. In my case, I'm using the Slick Code Generator to generate the model classes from a sql script.

I need the following to be able to dynamically access the attribute names, taken from Scala. Get field names list from case class:

import scala.reflect.runtime.universe._

def classAccessors[T: TypeTag]: List[MethodSymbol] = typeOf[T].members.collect {
    case m: MethodSymbol if m.isCaseAccessor => m
}.toList

A draft implementation for findByExample would be:

def findByExample[T, R](example: R) : Future[Seq[R]] = {
  var qt = TableQuery[T].result
  val accessors = classAccessors[R]
  (0 until example.productArity).map { i =>
    example.productElement(i) match {
      case None => // ignore
      case 0 => // ignore
      // ... some more default values => // ignore  
      // handle a populated case   
      case Some(x) => {
        val columnName = accessors(i)
        qt = qt.filter(_.columnByName(columnName) == x)
      }
    }
  }
  qt.result
}

But this doesn't work because I need better Scala Kungfu. T is the entity table type and R is the row type that is generated as a case class and therefore a valid Scala Product type.

The first problem in that code is that would be too inefficient because instead of doing e.g.

qt.filter(_.firstName === "Juan" && _.streetName === "Rosedale Ave." && _.streetNumber === 5)

is doing:

// find all
var qt = TableQuery[T].result
// then filter by each column at the time
qt = qt.filter(_.firstName === "Juan")
qt = qt.filter(_.streetName === "Rosedale Ave.")
qt = qt.filter(_.streetNumber === 5)

Second I can't see how to dynamically access the column name in the filter method i.e.

qt.filter(_.firstName == "Juan")

I need to instead have

qt.filter(_.columnByName("firstName") == "Juan")

but apparently there is no such possibility while using the filter function?

cvogt cvogt · Accepted Answer · 2016-11-28T02:37:16

Probably the best ways to implement filters and sorting by dynamically provided column names would be either plain SQL or extending the code generator to generate extension methods, something like this:

implicit class DynamicPersonQueries[C[_]](q: Query[PersonTable, PersonRow, C]){
  def dynamicFilter( column: String, value: String ) = column {
    case "firstName" => q.filter(_.firstName === value)
    case "streetNumber" => q.filter(_.streetNumber === value.toInt)
    ...
  }
}

You might have to fiddle with the types a bit to get it to compile (and ideally update this post afterwards :)).

You can then filter by all the provided values like this:

val examples: Map[String, String] = ...
val t = TableQuery[PersonTable]
val query = examples.foldLeft(t){case (t,(column, value)) => t.dynamicFilter(column, value)
query.result

Extending the code generator is explained here: http://slick.lightbend.com/doc/3.1.1/code-generation.html#customization

Slick: how to implement find by example i.e. findByExample generically?

2 Answers