I'm trying to create a typesafe dynamic DSL for a Slick table but not sure how to achieve this.
Users can post filters to the server by sending filters in form/json format, and I need to build a Slick query with all that.
So basically this means transforming a Scala case class representing my filters to a Slick query.
It seems the "predicates" can have 3 different shapes. I've seen the trait CanBeQueryCondition. Can I fold over these different possible shapes?
I've seen the extension methods && and || and know there is something to do with this but I just don't know how to do.
Basically, I have a list of predicates which takes the following types:
(PatientTable) => Column[Option[Boolean]]
or
(PatientTable) => Column[Boolean]
The problem to me is that there is not a single supertype for all the 3 different types that have a CanBeQueryCondition, so I don't really know how do fold the predicates with && as once added to the list these differently shaped predicate takes a very generic type List[(PatientTable) => Column[_ >: Boolean with Option[Boolean]]].
Also, I'm not sure about what can be considered a predicate in Slick. A composable predicate seems to be Column[Boolean], but actually the filter method only accept parameters of type (PatientTable) => Column[Boolean]
I'm answering my own question with what I've finally built.
Let's define a simple case class and row mapper
case class User(
id: String = java.util.UUID.randomUUID().toString,
companyScopeId: String,
firstName: Option[String] = None,
lastName: Option[String] = None
)
class UserTable(tag: Tag) extends Table[User](tag,"USER") {
override def id = column[String]("id", O.PrimaryKey)
def companyScopeId = column[String]("company_scope_id", O.NotNull)
def firstName = column[Option[String]]("first_name", O.Nullable)
def lastName = column[Option[String]]("last_name", O.Nullable)
def * = (id, companyScopeId, firstName, lastName) <>
(User.tupled,User.unapply)
}
Notion of predicate in Slick
I assume that the notion of "predicate" is what can be put inside TableQuery.filter. But this type is rather complex as it is a function that takes a Table and returns a type that has an implicit CanBeQueryCondition
Unfornunately for me there are 3 different types that have a CanBeQueryCondition and putting them in a list to be folded into a single predicate seems not easy (ie filter is easy to apply, but the && and || operators are hard to apply (as far as I've tried)). But fortunately it seems we can convert easily a Boolean to a Colunm[Boolean] to a Column[Option[Boolean]] with the .? extension method.
So let's define our predicate type:
type TablePredicate[Item, T <: Table[Item]] = T => Column[Option[Boolean]]
Folding a list of predicates (ie using conjunctions/disjunctions, ie composing AND and OR clauses)
Now we only have one type so we can easily fold a list of predicates into a single
// A predicate that never filter the result
def matchAll[Item, T <: Table[Item]]: TablePredicate[Item,T] = { table: T => LiteralColumn(1) === LiteralColumn(1) }
// A predicate that always filter the result
def matchNone[Item, T <: Table[Item]]: TablePredicate[Item,T] = { table: T => LiteralColumn(1) =!= LiteralColumn(1) }
def conjunction[Item, T <: Table[Item]](predicates: TraversableOnce[TablePredicate[Item, T]]): TablePredicate[Item,T] = {
if ( predicates.isEmpty ) matchAll[Item,T]
else {
predicates.reduce { (predicate1, predicate2) => table: T =>
predicate1(table) && predicate2(table)
}
}
}
def disjunction[Item, T <: Table[Item]](predicates: TraversableOnce[TablePredicate[Item, T]]): TablePredicate[Item,T] = {
if ( predicates.isEmpty ) matchNone[Item,T]
else {
predicates.reduce { (predicate1, predicate2) => table: T =>
predicate1(table) || predicate2(table)
}
}
}
The dynamic filtering case class
From these predicate primitives we can start creating our dynamic, composable and typesafe query DSL based on a case class.
case class UserFilters(
companyScopeIds: Option[Set[String]] = None,
firstNames: Option[Set[String]] = None,
lastNames: Option[Set[String]] = None
) {
type UserPredicate = TablePredicate[User,UserTable]
def withFirstNames(firstNames: Set[String]): UserFilters = this.copy(firstNames = Some(firstNames))
def withFirstNames(firstNames: String*): UserFilters = withFirstNames(firstNames.toSet)
def withLastNames(lastNames: Set[String]): UserFilters = this.copy(lastNames = Some(lastNames))
def withLastNames(lastNames: String*): UserFilters = withLastNames(lastNames.toSet)
def withCompanyScopeIds(companyScopeIds: Set[String]): UserFilters = this.copy(companyScopeIds = Some(companyScopeIds))
def withCompanyScopeIds(companyScopeIds: String*): UserFilters = withCompanyScopeIds(companyScopeIds.toSet)
private def filterByFirstNames(firstNames: Set[String]): UserPredicate = { table: UserTable => table.firstName inSet firstNames }
private def filterByLastNames(lastNames: Set[String]): UserPredicate = { table: UserTable => table.lastName inSet lastNames }
private def filterByCompanyScopeIds(companyScopeIds: Set[String]): UserPredicate = { table: UserTable => (table.companyScopeId.? inSet companyScopeIds) }
def predicate: UserPredicate = {
// Build the list of predicate options (because filters are actually optional)
val optionalPredicates: List[Option[UserPredicate]] = List(
firstNames.map(filterByFirstNames(_)),
lastNames.map(filterByLastNames(_)),
companyScopeIds.map(filterByCompanyScopeIds(_))
)
// Filter the list to remove None's
val predicates: List[UserPredicate] = optionalPredicates.flatten
// By default, create a conjunction (AND) of the predicates of the represented by this case class
conjunction[User,UserTable](predicates)
}
}
Notice the usage of .? for the companyScopeId field which permits to fit a non-optional column to our definition of a Slick predicate
Using the DSL
val Users = TableQuery(new UserTable(_))
val filter1 = UserFilters().withLastNames("lorber","silhol").withFirstName("robert")
val filter2 = UserFilters().withFirstName("sebastien")
val filter = disjunction[User,UserTable](Set(filter1.predicate,filter2.predicate))
val users = Users.filter(filter.predicate).list
// results in
// ( last_name in ("lorber","silhol") AND first_name in ("robert") )
// OR
// ( first_name in ("sebastien") )
Conclusion
This is far from being perfect but is a first draft and at least can give you some inspiration :) I would like Slick to make it easier to build such things that are very common in other query DSL (like Hibernate/JPA Criteria API)
See also this Gist for up-to-date solutions
"fold" is already the keyword here. Or "reduce" since you don't need a seeding value. buildFilter.reduce(_ && _)
Seems like to want a more general version of this: Dynamic OR filtering - Slick. I think my last example on this page is exactly what you want - it is just what cvogt proposes. I hope this helps.
I was looking for the same thing, and came across this question - the accepted answer was a very heavy inspiration to what I eventually landed on. Details are here.
The only comments I'd make about the accepted answer - TablePredicate[Item, T <: Table[Item]] can just be simplified to TablePredicate[T <: Table[_]] because Item is never used (at least in the sample). LiteralColumn(1) === LiteralColumn(1) can also just be LiteralColumn(Some(true)) (makes the generated queries slightly less awkward) - I'm pretty sure with a little more work, these could be eliminated entirely.
