Scala slick query where in list

问题:

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?

回答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

回答2:

It doesn't work because the StaticQuery object (Q) expects to implicitly set the parameters in the query string, using the type parameters of the query method to create a sort of setter object (of type scala.slick.jdbc.SetParameter[T]).
The role of SetParameter[T] is to set a query parameter to a value of type T, where the required types are taken from the query[...] type parameters.

From what I see there's no such object defined for T = List[A] for a generic A, and it seems a sensible choice, since you can't actually write a sql query with a dynamic list of parameters for the IN (?, ?, ?,...) clause

---

I did an experiment by providing such an implicit value through the following code

import scala.slick.jdbc.{SetParameter, StaticQuery => Q}

def seqParam[A](implicit pconv: SetParameter[A]): SetParameter[Seq[A]] = SetParameter {  
    case (seq, pp) =>
        for (a <- seq) {
            pconv.apply(a, pp)
        }
}

implicit val listSP: SetParameter[List[String]] = seqParam[String]

with this in scope, you should be able to execute your code

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)

But you must always manually guarantee that the locationCodes size is the same as the number of ? in your IN clause

---

In the end I believe that a cleaner workaround could be created using macros, to generalize on the sequence type. But I'm not sure it would be a wise choice for the framework, given the aforementioned issues with the dynamic nature of the sequence size.

回答3:

You can generate in clause automaticly like this:

  def find(id: List[Long])(implicit options: QueryOptions) = {
    val in = ("?," * id.size).dropRight(1)
    Q.query[List[Long], FullCard](s"""
        select 
            o.id, o.name 
        from 
            organization o
        where
            o.id in ($in)
        limit
            ?
        offset
            ?
            """).list(id ::: options.limits)
  }

And use implicit SetParameter as pagoda_5b says

  def seqParam[A](implicit pconv: SetParameter[A]): SetParameter[Seq[A]] = SetParameter {
    case (seq, pp) =>
      for (a <- seq) {
        pconv.apply(a, pp)
      }
  }

  implicit def setLongList = seqParam[Long]

回答4:

If you have a complex query and the for comprehension mentioned above is not an option, you can do something like the following in Slick 3. But you need to make sure you validate the data in your list query parameter yourself to prevent SQL injection:

val locationCodes = "'" + List("loc1","loc2","loc3").mkString("','") + "'"
sql"""
  select * from visit where visitor = $visitor 
    and location_code in (#$locationCodes)
"""

The # in front of the variable reference disables the type validation and allows you to solve this without supplying a function for the implicit conversion of the list query parameter.