Slick: dynamically create query / disjunction connections

Question

I am trying to create a dynamic DSL type for a Slick table, but I don’t know how to do it.

Users can send filters to the server by sending filters in form / json format, and I need to create a Slick request with all of this.

So basically this means converting the case Scala class representing my filters for a Slick request.

It seems that "predicates" can have 3 different forms. I saw the CanBeQueryCondition . Can I reset these various possible forms?

I saw extension methods && and || and I know that there is something to do with it, but I just don’t know how to do it.

Basically, I have a predicate list that takes the following types:

 (PatientTable) => Column[Option[Boolean]] 

or

 (PatientTable) => Column[Boolean] 

The problem for me is that for all three different types that have CanBeQueryCondition , there is not a single supertype, so I really don’t know how to hide predicates with && , once added to the list, this different predicate has a very common type List[(PatientTable) => Column[_ >: Boolean with Option[Boolean]]] .

Also, I'm not sure what can be considered a predicate in Slick. The composite predicate seems to be Column[Boolean] , but actually the filter method only accepts parameters of type (PatientTable) => Column[Boolean]

Answer 1

I answer my question with what I finally created.

Define a simple case and mapperper class

 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) } 

Slick predicate concept

I assume that the concept of "predicate" is what can be put inside TableQuery.filter . But this type is quite complex, because it performs the Table function and returns a type that has an implicit CanBeQueryCondition

Unfortunately for me there are 3 different types that have CanBeQueryCondition and putting them in a list that needs to be folded into a single predicate seems to be not easy (i.e. filter is easy to use, but && and || hard to apply (as far as I tried)) . But, fortunately, we can easily convert Boolean to Colunm[Boolean] to Column[Option[Boolean]] using the extension method .? .

So, let's define our predicate type:

 type TablePredicate[Item, T <: Table[Item]] = T => Column[Option[Boolean]] 

Adding a list of predicates (i.e. using conjunctions / disjunctions, i.e. making AND and OR sentences)

Now we have only one type, so we can easily collapse the list of predicates into one

  // 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) } } } 

---

Dynamic filter class class

From these predicate primitives, we can begin to create our dynamic, composite, and typical DSL queries based on the 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) } } 

Pay attention to use .? for the companyScopeId field, which allows you to put an optional column in our Slick predicate definition

Using 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 ideal, but the first project and, at least, can give you some inspiration :) I would like Slick to simplify the creation of things that are very common in other DSL requests (for example, the Hibernate / JPA Criteria API)

Answer 2

"fold" is already a keyword here. Or "reduce", since you do not need the value of the crop. buildFilter.reduce(_ && _)

Answer 3

It seems like a more general version is needed: Dynamic Filtering OR - Slick . I think my last example on this page - exactly what you want is what cvogt offers. Hope this helps.

Answer 4

I searched for the same thing and stumbled upon this question - the accepted answer was a very heavy inspiration for me to eventually land. Details here .

The only comments I would make about the accepted answer - TablePredicate[Item, T <: Table[Item]] can simply be simplified to TablePredicate[T <: Table[_]] , because the element is never used (at least in the sample). LiteralColumn(1) === LiteralColumn(1) can also be just LiteralColumn(Some(true)) (makes the generated queries a little less uncomfortable). I am sure that a little more work, they can be completely eliminated.