scala, slick, sub-select select, max column

slick sub-select with max value.md

I am learning slick. A bit of tough road initially. The blogs and other posts out there really help. This tutorial 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 <- 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 <- 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 <- Notes
      maxTimestamp <- 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
    }
  }