scala slick query return value

Question

I am rather new at Scala and have been struggling with slick and can't see how to return the results of a query to the calling method

I have a simple UserDto

case class UserDto(val firstName:String,
  val lastName:String,
  val userName:String,
  val isAdmin:Boolean) {}

a User table object

object User extends Table[(String, String, String, Boolean)]("USER") {

  def firstName = column[String]("FIRST_NAME")
  def lastName = column[String]("LAST_NAME")
  def userName = column[String]("USER_NAME")
  def admin = column[Boolean]("IS_ADMIN")

  def * = firstName ~ lastName ~ userName ~ admin

}

and a query class

class UserQuerySlickImpl(dataSource:DataSource) {

  def getResults(userName:String):Option[UserDto] = {
    var resultDto:Option[UserDto] = None

    Database.forDataSource(dataSource) withSession {
      val q = for {u <- User if u.userName is userName}
      yield (u.firstName, u.lastName, u.userName, u.admin)

      for (t <- q) {
        t match {
          case (f:String, l:String, u:String, a:Boolean) => 
            resultDto = Some(new UserDto(f, l, u, a))
        }
      }
    }
    resultDto
  }
}

I can query the database and get the user that matches the username, but the only way I could figure out how to return that user is by creating a var outside of the Database.forDataSource....{}.

Is there a better way that does not use the var but returns the resultDto directly.

also is there a way to construct the UserDto directly from the first for comprehension rather than needing the second for (t <- q) ...

I am using slick_2.10.0-M7, version 0.11.1.

Solution 1

I haven't toyed with Slick yet but if it's reasonable (by which I mean consistent with Scala conventions) you should be able to do something like

def getResults(userName:String):Option[UserDto] =
  Database.forDataSource(dataSource) withSession {
    val q = for {u <- User if u.userName is userName}
      yield (u.firstName, u.lastName, u.userName, u.admin)

    q.firstOption map { case (f, l, u, a) => UserDto(f, l, u, a) }
  }

This is exactly what you would do if q was a List[(String, String, String, Boolean)].

Cleaning this up a bit, you can write

def getResults(userName:String):Option[UserDto] =
  Database.forDataSource(dataSource) withSession {
    (for (u <- User if u.userName is userName)
      yield UserDto(u.firstName, u.lastName, u.userName, u.admin)).firstOption
  }

Otherwise, you should be able to use

q foreach { 
   case (f, l, u, a) => return Some(UserDto(f, l, u, a))
}
return None

Generally, return statements like this one should be avoided, so hopefully q's type gives you something more functional to work with.

Solution 2

Your q is a query, not a list of results. The presence of foreach might be a little confusing in that respect, but to obtain a List of results, you need to do q.list first. That gives you methods like map and foldLeft and so on.

If you want to get a single/the first result in an Option, use q.firstOption. Once you have that, you can map your function over the resulting 'Option[(...)]` to transform the tuple into the desired DTO.

An alternative way would be to specify a custom mapping that automatically maps your result tuples to some case class by using the <> operator, see http://slick.typesafe.com/doc/0.11.2/lifted-embedding.html#tables:

case class User(id: Option[Int], first: String, last: String) 

object Users extends Table[User]("users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def first = column[String]("first")
  def last = column[String]("last")
  def * = id.? ~ first ~ last <> (User, User.unapply _)
}

Comments

[Doug Anderson]

thanks for such a quick response, As it turns out, the val q does not have a head or headOption method but does support a foreach. This means that I can replace the 2nd for comprehension with q foreach { case (....) => resultDto = Some(UserDto( ... ) This is cleaner and answers the second question very nicely

[Doug Anderson]

I'm still not clear on how to get the resultDto out of the first Database.forDataSource(dataSource) withSession { ... } block without having to resort to an external var that is updated within the block

[Aaron Novstrup]

The withSession method takes a function parameter and returns the result of calling that function, so all you need to do is ensure that the value of the withSession block (i.e., the value of the last expression in the block) is the Option[UserDto] that you want to return. I'll clarify the code in my answer.

[Aaron Novstrup]

Hat tip to @eriksensei for firstOption rather than headOption, although I haven't checked the API to verify.

[Jack]

+1 Great answer - Where did you find this documented, ie. first, firstOption, etc? I could not locate it in the Scala Doc or anywhere else.

[Erik Post]

Thanks. :) The documentation is a bit of a patchwork thing for me. I cobble stuff together from looking at the docs I linked, the mailing list, StackOverflow and github.com/slick/slick-examples. The firstOption came from poking around in the source code. Looking at ScalaQuery stuff (e.g. projects on GitHub that use it) helps too. And then there's this ebook by Adam Mackler which looks quite useful.

[Brian]

that Some was key!