Is it possible to use IN clause in plain sql Slick?

21
votes

For example, I want to create the following query:

SELECT c.* FROM Coffees c WHERE c.name IN ('robusta', 'arabica')

My attempt failed:

val cnames = List("robusta", "arabica")
sql""" SELECT c.* FROM Coffees c WHERE c.name IN ${cnames} """
  could not find implicit value for parameter pconv: 
  scala.slick.jdbc.SetParameter[List[String]]

Is it possible to somehow use in clause in Slick plain sql queries?

4
scalascala-2.10slick
What puzzles me most, is that in Slick's lifted embedding, this is almost an effortless task. - Rogach
I use slick-pg and select * from Coffees where array_position(${cnames}, name) is not null. - Marcello Nuccio

4 Answers

2
votes

I don't see anything out of the box to handle this. You're best bet is probably something like this:

val cnames = List("robusta", "arabica").mkString("'", "','", "'")
val query = sql""" SELECT c.* FROM Coffees c WHERE c.name IN (${cnames}) """
32
votes

The type safe "lifted embedding" API supports this as well:

val ids = List(1,2,3)
val q = for {
  f <- Foo if f.id inSet ids // ids is not bound
}

slick.typesafe.com/doc/1.0.1/api/index.html#scala.slick.lifted.ColumnExtensionMethods

4
votes

Although it's not safe for SQL injection, you can use #$ interpolator:

val ids = idList.mkString("'", "','", "'")
val q = sql"""select name from mytable where id in (#$ids)"""
0
votes

There is a library that (among other things) introduces a binder for list properties to Slick's SQL interpolator: https://index.scala-lang.org/tarao/slick-jdbc-extension-scala/slick-jdbc-extension

Example code from the page:

  import util.NonEmpty

  def findAll(entryIds: Option[NonEmpty[Long]]): Seq[Entry] = entryIds match {
    case Some(ids) => run { sql"""
    | SELECT * FROM ${table}
    | WHERE entry_id IN $ids
    """.as[Entry] }
    case None => Seq.empty
  }