scala slick and sub-selects for max value

I am learning slick. A bit of tough road initially. The blogs and other posts out there really help. Thistutorial was especially helpful.

My domain was storing note objects in database and tracking revisions. Here's the table structure:

 class Notes(tag: Tag) extends Table[(Int, Int, java.sql.Timestamp, Boolean, Option[String])](tag, "Notes") {
    // some dbs cannot return compound primary key, so use a standard int
    def id = column[Int]("id", O.AutoInc, O.PrimaryKey)
    def docId = column[Int]("docId")
    def createdOn = column[java.sql.Timestamp]("createdOn")
    def content = column[Option[String]]("content")
    def latest = column[Boolean]("latest")
    def * = (id, docId, createdOn, latest, content)

    def index1 = index("index1", docId)
    def index2 = index("index2", createdOn)
    def index3 = index("index3", latest)
  }
  val Notes = TableQuery[Notes]

There are more enhancements needed for this table, but you get the gist.

So we can query the table for a specific document id and obtain the latest document:

 def latestNote(docId: Int) =
    for {
      n <- font="" notes="">
      if n.docId === docId;
      if n.latest === true
    } yield n

But if we want to find it using just the timestamp, the first thing you can do is issue 2 queries:

 def latestFromTimestamp(docId: Int)(implicit session: Session) = {
    val maxTimestamp = Notes.groupBy(_.docId).map {
      case (docId, note) => note.map(_.createdOn).max
    }
    maxTimestamp.firstOption match {
      case Some(ts) =>
        (for { n <- font="" notes=""> if n.createdOn === ts } yield n).firstOption
      case _ => None
    }
  }

But we would like to avoid issuing 2 queries because then we need a Session object. We want more of a query:

 def latestFromTimestamp2(docId: Int) = {
    for {
      n <- font="" notes="">
      maxTimestamp <- font="" notes="">.groupBy(_.docId).map { case (docId, note) => note.map(_.createdOn).max }
      if n.createdOn === maxTimestamp
    } yield n
  }

which generates:

query: select x2."id", x2."docId", x2."createdOn", x2."latest", x2."content" from "blah" x2, (select max(x3."createdOn") as x4 from "blah" x3 group by x3."docId") x5 where x2."createdOn" = x5.x4

and that's what we wanted.

Of course, if we want to update a Note's latest flag, we do need to run 2 queries and hence need a session:

 def update(docId: Int, content: Option[String])(implicit session: Session) = {
    val note = latestNote(docId).firstOption
    note match { 
      case Some(n) =>
        // Update old note
        Notes.filter(_.docId === docId).map(_.latest).update(false)
        // Add insert the new note
        val newNote = (n._1, n._2, now, true, content)
        val newId = (Notes returning Notes.map(_.id)) insert newNote
        Some((newId, n._2, n._3, n._4, n._5))
      case _ => None
    }
  }

This is on gisthub: gisthub