Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

scala slick query return value

Tags:

scala

slick

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.

like image 520
Doug Anderson Avatar asked Oct 27 '12 05:10

Doug Anderson


2 Answers

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 _)
}
like image 119
Erik Post Avatar answered Sep 19 '22 17:09

Erik Post


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.

like image 28
Aaron Novstrup Avatar answered Sep 19 '22 17:09

Aaron Novstrup