How to return a List[User] when using sql with slick

Question

I can seem to get how I can return a List[User] from a raw sql query.

implicit val getUserResult = GetResult(r => User(r.nextInt, ....))

sql"""
select * from users where id =1

""".as[User]

This seems to compile fine, but if I change this to return a list and not a single result I get an error.

.as[List[User]]

Error:

could not find implicit value for parameter rconv: scala.slick.jdbc.GetResult[List[User]]

How can I change the implicit GetResult to return a list?

Update

So I created the implicit like:

implicit val getUserResult = GetResult( r => User(r.nextInt, ....))

val query: StaticQuery0[User] = sql"....."

query.list()

I can this error:

[PSQLException: Bad value for type int : asdf asdfs]

In psql I can see that I have "asdf asdfs" in some columns, but not INT columns. If things compile I'm not sure why I am getting this error, looks like a bug?

This query will return one User since it looks for id which I would assume is unique. You can still return this query .as[User] but append Nil like .as[User] :: Nil. — goral
@goral yes I will change the query thanks, but I was trying to emphasis how to get a list of users back. the query wasn't the focus. — Blankman

Ende Neu Ende Neu · Accepted Answer · 2014-08-30T08:19:54

As @goral said you will probably only get one row since you filter by id, nevertheless you can easily get a list:

import scala.slick.driver.JdbcDriver.backend.Database
import Database.dynamicSession
import scala.slick.jdbc.{GetResult, StaticQuery => Q}
import Q.interpolation

val t: StaticQuery0[User] = 
  sql"select * from users where id = 1".as[User]

val list: List[User] = t.list()

StaticQuery has all the common methods like list, first, firstOption and so on, since they are inherited from the Invoker trait.

If you want to use a custom case class and use a GetResult:

case class User(id: Int, name: String)

implicit val getUserResult = GetResult(r => User(r.nextInt, r.nextString))

val t: StaticQuery0[User] = sql"select * from adwords_ads where name = ".as[User]
val list: List[User] = t.list()

Edit:

It's working for me even without the implicit mapper because the slick schema generator has already generated one for me:

/** GetResult implicit for fetching SalesforceLogRow objects using plain SQL queries */
implicit def GetResultSalesforceLogRow(implicit e0: GR[Long], e1: GR[String], e2: GR[Int], e3: GR[java.sql.Timestamp], e4: GR[Option[java.sql.Timestamp]]): GR[myEntity] = GR {
  prs => import prs._
    myEntity.tupled((<<[Long], <<[Long], <<[String], <<[Int], <<[java.sql.Timestamp], <<[Long], <<?[java.sql.Timestamp]))
}

I usually have it in a schema object, apparently you don't have it and Slick doesn't know how to map your plain SQL.

How to return a List[User] when using sql with slick

1 Answers