Scala 圆滑的查询在列表中的位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14064382/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Scala slick query where in list
提问by ShatyUT
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:
我正在尝试学习使用 Slick 来查询 MySQL。我有以下类型的查询来获取单个访问对象：
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:
我想知道的是如何更改上面的查询以获取位置集合的 List[Visit] ......像这样：
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?
Slick 可以做到这一点吗？
回答by Faiz
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.sizeis always equal to the number of (?, ?...)you have in your query is brittle.
正如另一个答案所暗示的那样，这对于静态查询来说很麻烦。静态查询接口要求您将绑定参数描述为Product. (Int, Int, String*)不是有效的scala，使用也(Int,Int,List[String])需要一些kludges。此外，必须确保locationCodes.size始终等于(?, ?...)您在查询中拥有的数量是脆弱的。
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.
在实践中，这不是什么大问题，因为您希望使用查询 monad，这是使用 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
回答by pagoda_5b
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 querymethod 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.
它不起作用，因为StaticQuery object( Q) 期望隐式设置查询字符串中的参数，使用方法的类型参数query创建一种 setter 对象（类型scala.slick.jdbc.SetParameter[T]）。
的作用SetParameter[T]是将查询参数设置为 type 的值T，其中需要的类型取自query[...]类型参数。
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
从我看到的情况来看，没有T = List[A]为 generic定义这样的对象A，这似乎是一个明智的选择，因为您实际上无法使用IN (?, ?, ?,...)子句的动态参数列表编写 sql 查询
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 locationCodessize is the same as the number of ?in your INclause
但是您必须始终手动保证locationCodes大小与?您的IN子句中的数量相同
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.
最后，我相信可以使用宏创建更清晰的解决方法，以概括序列类型。但鉴于上述序列大小的动态性质问题，我不确定这是否是该框架的明智选择。
回答by caiiiycuk
You can generate in clause automaticly like this:
您可以像这样自动生成 in 子句：
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
并使用隐式 SetParameter 作为pagoda_5b 说
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]
回答by markus
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:
如果您有一个复杂的查询并且上面提到的 for comprehension 不是一个选项，您可以在 Slick 3 中执行以下操作。但是您需要确保自己验证列表查询参数中的数据以防止 SQL 注入：
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.
变量引用前面的 # 禁用类型验证，并允许您解决此问题，而无需为列表查询参数的隐式转换提供函数。
