Scala slick query where in list

Question

I am attempting to learn to use Slick to query MySQL. I have the following type of query working to get a single Visit object:

Q.query[(Int,Int), Visit]("""
    select * from visit where vistor = ? and location_code = ?
""").firstOption(visitorId,locationCode)

What I would like to know is how can I change the above to query to get a List[Visit] for a collection of Locations...something like this:

val locationCodes = List("loc1","loc2","loc3"...)
Q.query[(Int,Int,List[String]), Visit]("""
    select * from visit where vistor = ? and location_code in (?,?,?...)
""").list(visitorId,locationCodes)

Is this possible with Slick?

Answer 1

As the other answer suggests, this is cumbersome to do with static queries. The static query interface requires you to describe the bind parameters as a Product. (Int, Int, String*) is not valid scala, and using (Int,Int,List[String]) needs some kludges as well. Furthermore, having to ensure that locationCodes.size is always equal to the number of (?, ?...) you have in your query is brittle.

In practice, this is not too much of a problem because you want to be using the query monad instead, which is the type-safe and recommended way to use Slick.

val visitorId: Int = // whatever
val locationCodes = List("loc1","loc2","loc3"...)
// your query, with bind params.
val q = for {
    v <- Visits 
    if v.visitor is visitorId.bind
    if v.location_code inSetBind locationCodes
  } yield v
// have a look at the generated query.
println(q.selectStatement)
// run the query
q.list

This is assuming you have your tables set up like this:

case class Visitor(visitor: Int, ... location_code: String)

object Visitors extends Table[Visitor]("visitor") {
  def visitor = column[Int]("visitor")
  def location_code = column[String]("location_code")
  // .. etc
  def * = visitor ~ .. ~ location_code <> (Visitor, Visitor.unapply _)
}

Note that you can always wrap your query in a method.

def byIdAndLocations(visitorId: Int, locationCodes: List[String]) = 
  for {
    v <- Visits 
    if v.visitor is visitorId.bind
    if v.location_code inSetBind locationCodes
  } yield v
}

byIdAndLocations(visitorId, List("loc1", "loc2", ..)) list