Scala光滑查询在列表中的位置 [英] Scala slick query where in list

问题描述

我正在尝试学习使用Slick查询MySQL.我使用以下类型的查询来获取单个Visit对象:

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)

使用Slick可以吗?

Is this possible with Slick?

推荐答案

另一个答案表明，使用静态查询很麻烦.静态查询界面要求您将绑定参数描述为Product. (Int, Int, String*) 是无效的Scala，并且使用(Int,Int,List[String])也需要一些技巧.此外，必须确保locationCodes.size始终等于查询中具有的(?, ?...)数，这很容易.

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.

实际上，这并不是什么大问题，因为您想改为使用查询monad，这是使用Slick的类型安全且推荐的方式.

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

这篇关于Scala光滑查询在列表中的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！