CODEWITHSUNDEEP
scalaslick
Feyyaz
Feyyaz

Reputation: 3216

Slick Plain SQL Query with Dynamic Conditions

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.

Upvotes: 9

Views: 3078

Answers (2)

trydofor
trydofor

Reputation: 316

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

Upvotes: 10

pumpump
pumpump

Reputation: 382

I think the bind variables are not interpreted by the map function. They are interpreted by Slick sql interpolator. That's why your sql2 will not get the surname value.

If you need to compose SQL statements, perhaps you can consider not using the Plain SQL feature. Can you just do .filter(.name ==== name ).filter(.col2 === surname)?

Upvotes: 0

Related Questions