Select single row based on Id in Slick

Question

I want to query a single row from user based on Id. I have following dummy code

case class User(
    id: Option[Int], 
    name: String
}

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

  def findById(userId: Int)(implicit session: Session): Option[User] = {
    val user = this.map { e => e }.where(u => u.id === userId).take(1)
    val usrList = user.list
    if (usrList.isEmpty) None
    else Some(usrList(0))
  }
}

It seems to me that findById is a overkill to query a single column as Id is standard primary key. Does anyone knows any better ways? Please note that I am using Play! 2.1.0

Solution 1

Use headOption method in Slick 3.*:

  def findById(userId: Int): Future[Option[User]] ={
    db.run(Users.filter(_.id === userId).result.headOption)
  }

Solution 2

You could drop two lines out of your function by switching from list to firstOption. That would look like this:

def findById(userId: Int)(implicit session: Session): Option[User] = {
  val user = this.map { e => e }.where(u => u.id === userId).take(1)
  user.firstOption
}

I believe you also would do your query like this:

def findById(userId: Int)(implicit session: Session): Option[User] = {
  val query = for{
    u <- Users if u.id === userId
  } yield u
  query.firstOption
}

Solution 3

firstOption is a way to go, yes.

Having

  val users: TableQuery[Users] = TableQuery[Users]

we can write

def get(id: Int): Option[User] = users.filter { _.id === id }.firstOption

Comments

[Peter Becich]

Do you think firstOption may have been removed from lifted.Query in Slick 2.1? I believe it has been. slick.typesafe.com/doc/2.1.0/api/#scala.slick.lifted.Query

[BrokenGlass]

It's gone - this answer is outdated now

[Tadej Mali]

Is there a way to do something similar as in LINQ where there is a .First() method and also a .Single() method? .First simply returns the first "row" and .Single throws an exception if there is more than 1 row in the result set.