Slick Plain SQL Query with Dynamic Conditions

Question

I'm struggling on appending additional conditions to my query. In its simplest form, what I need is 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 #$ could be an option, but then surname wouldn't be a bind variable, which is a big issue.

trydofor trydofor · Accepted Answer · 2016-04-16T07:44:44

here is an sample 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

Slick Plain SQL Query with Dynamic Conditions

2 Answers