Dynamic Filtering OR - Slick

Question

Ok, I have a method with a few optional arguments like

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

and I want to write a dynamic query that will be able to retrieve the data of certain arguments in a way

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

so depending on what arguments are defined to add them to the query with the OR operator?

Answer 1

Something like this should work. I had to use a similar snippet in my own code, and it is also close to what cvogt offers in the 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

Thoefer is good for simple use cases, but has some limitations. For example, if all your parameters are None, the list is empty and you cannot reduce the empty list :)

If you need something more complex, based on predicate, unions and clauses (a bit like the Hibernate / JPA Criteria API), you can check my answer in Slick: create query / clause connections dynamically