光滑:使用Column [Int]值时遇到困难 [英] Slick: Difficulties working with Column[Int] values

问题描述

我对我最近问过的另一个Slick问题进行了跟踪(

I have a followup to another Slick question I recently asked (Slick table Query: Trouble with recognizing values) here. Please bear with me!! I'm new to databasing and Slick seems especially poor on documentation. Anyway, I have this table:

object Users extends Table[(Int, String)]("Users") {

  def userId          = column[Int]("UserId", O.PrimaryKey, O.AutoInc)
  def userName        = column[String]("UserName")

  def * = userId ~ userName
}

第一部分

Part I

我正在尝试使用此功能进行查询:

I'm attempting to query with this function:

def findByQuery(where: List[(String, String)]) = SlickInit.dbSlave withSession {    

  val q = for {
    x <- Users if foo((x.userId, x.userName), where)
           } yield x
      q.firstOption.map { case(userId, userName) =>
                    User(userId, userName)}
   }

where"where"是搜索查询列表///. ("userId"，"1")，("userName"，"Alex") "foo"是一个测试相等性的辅助函数.我遇到类型错误.
x.userId的类型为Column [Int].如何将其作为Int操作呢?我尝试过投射，例如:

where "where" is a list of search queries //ex. ("userId", "1"),("userName", "Alex") "foo" is a helper function that tests equality. I'm running into a type error.
x.userId is of type Column[Int]. How can one manipulate this as an Int? I tried casting, ex:

foo(x.userId.asInstanceOf[Int]...)

但与此同时也遇到了麻烦.一个如何处理Slick返回类型?

but am also experiencing trouble with that. How does one deal with Slick return types?

第二部分 有谁熟悉转换功能:

Part II Is anyone familiar with the casting function:

def * = userId〜userName<>(用户，User.unapply _)

def * = userId ~ userName <> (User, User.unapply _)

?我知道这个问题有很好的答案，尤其是在这里:在SLICK中将伴随对象映射到投影.但是谁能解释为什么编译器以

? I know there have been some excellent answers to this question, most notably here: scala slick method I can not understand so far and a very similar question here: mapped projection with companion object in SLICK. But can anyone explain why the compiler responds with

<> method overloaded 

那简单的代码行吗?

推荐答案

让我们从问题入手:

val q = for {
    x <- Users if foo((x.userId, x.userName), where)
} yield x

请参阅，Slick将Scala表达式转换为SQL.为了能够根据需要将条件转换为SQL语句，Slick需要使用一些特殊类型.这些类型的工作方式实际上是Slick执行的转换的一部分.

See, Slick transforms Scala expressions into SQL. To be able to transform conditions, as you want, into SQL statement, Slick requires some special types to be used. The way these types works are actually part of the transformation Slick performs.

例如，当您编写List(1,2,3) filter { x => x == 2 }时，将对列表中的每个元素执行过滤谓词.但是Slick无法做到这一点！因此Query [ATable]过滤器{arow => arow.id === 2}实际上意味着以条件id = 2进行选择"(我在这里跳过了细节).

For example, when you write List(1,2,3) filter { x => x == 2 } the filter predicate is executed for each element in the list. But Slick can't do that! So Query[ATable] filter { arow => arow.id === 2 } actually means "make a select with the condition id = 2" (I am skipping details here).

我为您的foo函数编写了一个模拟文件，并要求Slick为查询q生成SQL:

I wrote a mock of your foo function and asked Slick to generate the SQL for the query q:

select x2."UserId", x2."UserName" from "Users" x2 where false

看到了false吗?这是因为foo是Scala评估为布尔值false的简单谓词.在查询中完成的类似谓词(而不是列表)将评估为SQL生成中需要完成的工作的描述.比较List和Slick中的filter s之间的区别:

See the false? That's because foo is a simple predicate that Scala evaluates into the Boolean false. A similar predicate done in a Query, instead of a list, evaluates into a description of a what needs to be done in the SQL generation. Compare the difference between the filters in List and in Slick:

List[A].filter(A => Boolean):List[A]
Query[E,U].filter[T](f: E => T)(implicit wt: CanBeQueryCondition[T]):Query[E,U]  

列表过滤器评估为As列表，而Query.filter评估为新查询！

List filter evaluates to a list of As, while Query.filter evaluates into new Query!

现在，迈向解决方案的一步.

Now, a step towards a solution.

似乎您想要的实际上是SQL的in运算符.如果列表中有一个元素，则in运算符将返回true，例如:4 in (1,2,3,4)为true.请注意，(1,2,3,4)是一个 SQL列表，而不是Scala中的 Tuple .

It seems that what you want is actually the in operator of SQL. The in operator returns true if there is an element in a list, eg: 4 in (1,2,3,4) is true. Notice that (1,2,3,4) is a SQL list, not Tuple like in Scala.

对于in SQL运算符的这种使用情况，Slick使用运算符inSet.

For this use case of the in SQL operator Slick uses the operator inSet.

现在是问题的第二部分. (我将where变量重命名为list，因为where是一种Slick方法)

Now comes the second part of the problem. (I renamed the where variable to list, because where is a Slick method)

您可以尝试:

val q = for {
  x <- Users if (x.userId,x.userName) inSet list
} yield x

但是那不会编译！这是因为SQL不像Scala那样具有元组.在SQL中，您无法执行(1,"Alfred") in ((1,"Alfred"),(2, "Mary"))(请记住，(x,y,z)是列表的SQL语法，我在这里滥用语法只是为了表明它是无效的-还有很多SQL的方言，它是可能其中一些确实以类似的方式支持元组和列表.)

But that won't compile! That's because SQL doesn't have Tuples the way Scala has. In SQL you can't do (1,"Alfred") in ((1,"Alfred"),(2, "Mary")) (remember, the (x,y,z) is the SQL syntax for lists, I am abusing the syntax here only to show that it's invalid -- also there are many dialects of SQL out there, it is possible some of them do support tuples and lists in a similar way.)

一种可能的解决方案是仅使用userId字段:

One possible solution is to use only the userId field:

val q = for {
  x <- Users if x.userId inSet list2
} yield x

这会生成select x2."UserId", x2."UserName" from "Users" x2 where x2."UserId" in (1, 2, 3)

但是，由于您显式地使用了用户ID和用户名，因此可以合理地假设用户ID不能唯一地标识用户.因此，为了进行修改，我们可以将两个值连接在一起.当然，我们需要在列表中做同样的事情.

But since you are explicitly using user id and user name, it's reasonable to assume that user id doesn't uniquely identify a user. So, to amend that we can concatenate both values. Of course, we need to do the same in the list.

val list2 = list map { t => t._1 + t._2 }
val q2 = for {
  x <- Users if (x.userId.asColumnOf[String] ++ x.userName) inSet list2
} yield x

查看生成的SQL:

select x2."UserId", x2."UserName" from "Users" x2 
where (cast(x2."UserId" as VARCHAR)||x2."UserName") in ('1a', '3c', '2b')

看到上面的||吗?这是H2Db中使用的字符串连接运算符. H2Db是我用来运行示例的Slick驱动程序.此查询和其他查询可能会略有不同，具体取决于您所使用的数据库.

See the above ||? It's the string concatenation operator used in H2Db. H2Db is the Slick driver I am using to run your example. This query and the others can vary slightly depending on the database you are using.

希望它能阐明浮油的工作原理并解决您的问题.至少是第一个. :)

Hope that it clarifies how slick works and solve your problem. At least the first one. :)

这篇关于光滑:使用Column [Int]值时遇到困难的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！