i have followup slick question asked (slick table query: trouble recognizing values) here. please bear me!! i'm new databasing , slick seems poor on documentation. anyway, have 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 query function:
def findbyquery(where: list[(string, string)]) = slickinit.dbslave withsession { val q = { x <- users if foo((x.userid, x.username), where) } yield x q.firstoption.map { case(userid, username) => user(userid, username)} } where "where" list of search queries //ex. ("userid", "1"),("username", "alex") "foo" helper function tests equality. i'm running type error.
x.userid of type column[int]. how can 1 manipulate int? tried casting, ex:
foo(x.userid.asinstanceof[int]...) but experiencing trouble that. how 1 deal slick return types?
part ii familiar casting function:
def * = userid ~ username <> (user, user.unapply _)
? know there have been excellent answers question, notably here: scala slick method can not understand far , similar question here: mapped projection companion object in slick. can explain why compiler responds
<> method overloaded for simple line of code?
let's start problem:
val q = { x <- users if foo((x.userid, x.username), where) } yield x see, slick transforms scala expressions sql. able transform conditions, want, sql statement, slick requires special types used. way these types works part of transformation slick performs.
for example, when write list(1,2,3) filter { x => x == 2 } filter predicate executed each element in list. slick can't that! query[atable] filter { arow => arow.id === 2 } means "make select condition id = 2" (i skipping details here).
i wrote mock of foo function , asked slick generate sql query q:
select x2."userid", x2."username" "users" x2 false see false? that's because foo simple predicate scala evaluates boolean false. similar predicate done in query, instead of list, evaluates description of needs done in sql generation. compare difference between filters in list , 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 list of as, while query.filter evaluates new query!
now, step towards solution.
it seems want in operator of sql. in operator returns true if there element in list, eg: 4 in (1,2,3,4) true. notice (1,2,3,4) sql list, not tuple in scala.
for use case of in sql operator slick uses operator inset.
now comes second part of problem. (i renamed where variable list, because where slick method)
you try:
val q = { x <- users if (x.userid,x.username) inset list } yield x but won't compile! that's because sql doesn't have tuples way scala has. in sql can't (1,"alfred") in ((1,"alfred"),(2, "mary")) (remember, (x,y,z) sql syntax lists, abusing syntax here show it's invalid -- there many dialects of sql out there, possible of them support tuples , lists in similar way.)
one possible solution use userid field:
val q = { x <- users if x.userid inset list2 } yield x this generates select x2."userid", x2."username" "users" x2 x2."userid" in (1, 2, 3)
but since explicitly using user id , user name, it's reasonable assume user id doesn't uniquely identify user. so, amend can concatenate both values. of course, need same in list.
val list2 = list map { t => t._1 + t._2 } val q2 = { x <- users if (x.userid.ascolumnof[string] ++ x.username) inset list2 } yield x look generated sql:
select x2."userid", x2."username" "users" x2 (cast(x2."userid" varchar)||x2."username") in ('1a', '3c', '2b') see above ||? it's string concatenation operator used in h2db. h2db slick driver using run example. query , others can vary depending on database using.
hope clarifies how slick works , solve problem. @ least first one. :)
Comments
Post a Comment