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!

score 9 · Accepted Answer · edited Jul 18 '13 at 14:03

9

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

edited Jul 18 '13 at 14:03

damian

1,419
1
22
41

answered Jul 18 '13 at 14:00

Nilanjan

741
4
9

If I use the first solution you posted I get `error in SQL syntax near 'OPTION SQL_SELECT_LIMIT=DEFAULT'` – damian Jul 18 '13 at 14:09
3

Could be a driver issue. Take a look at http://stackoverflow.com/questions/15113707/error-code-1064-sql-state-42000-you-have-an-error-in-your-sql-syntax – Nilanjan Jul 18 '13 at 17:36
2

Updated to `"mysql" % "mysql-connector-java" % "5.1.25"`. Works great, thanks! – damian Jul 18 '13 at 17:50
@Nilanjan bit nicer, "val byId = t.createFinderBy(_.id)" – virtualeyes Jul 21 '13 at 11:30

score 4 · Answer 2 · answered Jul 18 '13 at 13:54

4

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

answered Jul 18 '13 at 13:54

alno

3,556
1
19
10

Or even `.first` if you are sure your Query is returning an object. And isn't it `Query(Category)...`? – i.am.michiel Jul 18 '13 at 13:57
@alno I had actually already switch to the filter version and just didn't update my code here. But using `Category.filter(_.id === id).firstOption` gives me `type mismatch; found : Option[scala.slick.lifted.NothingContainer#TableNothing] required: Option[models.Category] ` which is why I switched to the list.headOption in the first place. – damian Jul 18 '13 at 13:59
Just tested - I get Option[scala.slick.lifted.NothingContainer#TableNothing] in both variants, wrapping table in Query(Category) helps. – alno Jul 18 '13 at 14:16
@alno when I use `Query(Category)` I get a sql error `error in SQL syntax near 'OPTION SQL_SELECT_LIMIT=DEFAULT'` – damian Jul 18 '13 at 14:22

Goku__ · Answer 3 · 2015-05-22T07:18:00.553

I am using slick 1.0.1 with Play 2.2.1 and the following works for me.

val byId = createFinderBy(_.id)

Then call it from a method.

  def findById(id: Int): Option[Category] = DB.withSession { implicit session =>
    Category.byId(id).firstOption
  }

Please note, the DB.withSession is a method from the play framework.

If you are not using Play, the method would be something like below.

  def findById(id: Int)(implicit session: Session): Option[Category] = {
    Category.byId(id).firstOption
  }

Slick select row by id

3 Answers3