Slick select row by id

Question

Selecting a single row by id should be a simple thing to do, yet I'm having a bit of trouble figuring out how to map this to my object.

I found this question which is looking for the same thing but the answer given does not work for me.

Currently I have this that is working, but it doesn't seem as elegant as it should be.

def getSingle(id: Long):Option[Category] = withSession{implicit session =>
 (for{cat <- Category if cat.id === id} yield cat ).list.headOption
 //remove the .list.headOption and the function will return a WrappingQuery
}

I feel getting a list then taking headOption is just bulky and unnecessary. I must be missing something.

If it helps, here is more of my Category code

case class Category(
  id: Long = 0L,
  name: String
)
object Category extends Table[Category]("categories"){

  def name = column[String]("name", O.NotNull)
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

  def * = id ~ name <> (Category.apply _, Category.unapply _)

  ...
}

Is there an easier way to just get an Option[T] from an ID using Slick?

Solution There was a driver issue. I couldn't use .firstOption but upgraded to mysql jdbc 5.1.25 and all is well!

Answer 1

You can do this:

def getSingle(id: Long):Option[Category] = withSession{implicit session =>
 Query(Category).where(_.id === id).firstOption 
}

If you use this query quite often then you should consider QueryTemplate:

val byId = t.createFinderBy( t => t.id )

This will create a precompiled prepared statement that you can use from your method

def getSingle(id: Long):Option[Category] = byId(id).firstOption

Answer 2

Firstly, you may try is to use desugared version of the same code:

Category.filter{ _.id === id }.list.headOption

It looks much cleaner.

Also you may use firstOption method:

Category.filter{ _.id === id }.firstOption