Dynamic OR filtering - Slick

Question

Ok, I've got a method with multiple optional arguments like this

def(username: Option[String], petname: Option[String], favouritefood: Option[String])

and i want to write a dynamic query that will be capable of fetching the data of defined arguments in a way of this

select * from table where un like username or pn like pn or ff like ff;

so depending of which arguments are defined to add them to query with OR operator?

Answer 1

Something like this should work. I had to use a similiar fragment in my own code and it is also close to what cvogt proposes in above comment (I think).

val username = Option("")
val petname = Option("")
val ff:Option[String] = None

val default = LiteralColumn(1) === LiteralColumn(1) 

yourTable.filter { it => 
  List(
      username.map(it.username === _),
      petname.map(it.petname === _),
      ff.map(it.ff === _)
  ).collect({case Some(it)  => it}).reduceLeftOption(_ || _).getOrElse(default)
}

Answer 2

The thoefer is nice for simple use cases but has some limits. Like if all your options are None's the list is empty and you can't reduce an empty list :)

If you need something more composable, based on predicate, conjunctions and disjunctions (a bit like Hibernate/JPA Criteria API), you can check my answer in Slick: create query conjunctions/disjunctions dynamically