Slick Plain SQL Query with dynamic conditions

Question

I am trying to add additional conditions to my request. In its simplest form, I need something like below:

def findPeople(name: String, maybeSurname: Option[String]) = { val sql1 = sql"select * from my_table where name = $name" val sql2 = maybeSurname.map( surname => sql"and col2 = $surname" ).getOrElse(sql"") val finalSql = sql1 + sql2 // I need this kind of feature ... ... } 

Using # $ may be an option, but then the last name will not be variable binding, which is a big problem.

Answer 1

here is an example test on slick 3.1.x

 import slick.jdbc.{SQLActionBuilder, SetParameter, PositionedParameters} object SlickKit { implicit class SQLActionBuilderConcat (a: SQLActionBuilder) { def concat (b: SQLActionBuilder): SQLActionBuilder = { SQLActionBuilder(a.queryParts ++ b.queryParts, new SetParameter[Unit] { def apply(p: Unit, pp: PositionedParameters): Unit = { a.unitPConv.apply(p, pp) b.unitPConv.apply(p, pp) } }) } } } 

and then

 import SlickKit._ val sql1 = sql""" select count(*) from idinfo_#$i """ val sql2 = sql""" where source=$source """ val sql = sql1 concat sql2 sql.as[Int].head 

Answer 2

I think binding variables are not interpreted by the mapping function. They are interpreted by the Slick sql interpolator. This is why your sql2 will not get the last name value.

If you need to compile SQL statements, perhaps you can not use the Plain SQL function. Can you just do .filter (.name ==== name) .filter (.col2 === last name)?