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" 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]...)
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, User.unapply _)
? 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
for that simple line of code?
Let's start with the problem:
val q = for {
x <- Users if foo((x.userId, x.userName), where)
} yield x
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.
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).
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
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]
List filter evaluates to a list of As, while Query.filter evaluates into new Query!
Now, a step towards a solution.
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.
For this use case of the in SQL operator Slick uses the operator inSet.
Now comes the second part of the problem. (I renamed the where variable to list, because where is a Slick method)
You could try:
val q = for {
x <- Users if (x.userId,x.userName) inSet list
} yield x
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.)
One possible solution is to use only the userId field:
val q = for {
x <- Users if x.userId inSet list2
} yield x
This generates select x2."UserId", x2."UserName" from "Users" x2 where x2."UserId" in (1, 2, 3)
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
Look the generated SQL:
select x2."UserId", x2."UserName" from "Users" x2
where (cast(x2."UserId" as VARCHAR)||x2."UserName") in ('1a', '3c', '2b')
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. :)
Part I:
Slick uses Column[...]-types instead of ordinary Scala types. You also need to define Slick helper functions using Column types. You could implement foo like this:
def foo( columns: (Column[Int],Column[String]), values: List[(Int,String)] ) : Column[Boolean] = values.map( value => columns._1 === value._1 && columns._2 === value._2 ).reduce( _ || _ )
Also read pedrofurla's answer to better understand how Slick works.
Part II:
Method <> is indeed overloaded and when types don't work out the Scala compiler can easily become uncertain which overload it should use. (We should get rid of the overloading in Slick I think.) Writing
def * = userId ~ userName <> (User.tupled _, User.unapply _)
may slightly improve the error message you get. To solve the problem make sure that the Column types of userId and userName exactly correspond to the member types of your User case class, which should look something like case class User( id:Int, name:String ). Also make sure, that you extend Table[User] (not Table[(Int,String)]) when mapping to User.
