Skip to content

Instantly share code, notes, and snippets.

@aappddeevv
Last active October 12, 2018 06:57
Show Gist options
  • Save aappddeevv/8768593 to your computer and use it in GitHub Desktop.
Save aappddeevv/8768593 to your computer and use it in GitHub Desktop.
scala, slick, sub-select select, max column

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
    }
  }
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment