Select rows based on MAX values of a column in ScalaQuery/SLICK

Question

Say that i have table such as:

UserActions
    UserId INT
    ActionDate TIMESTAMP
    Description TEXT

that holds dates where users perfomed certainActions. If i wanted to get the last action that every user perfomed, i would have to do something like this in SQL:

SELECT *
FROM   UserActions,
       (
           SELECT ua.UserId,
                  max(ua.ActionDate) AS lastActionDate
           FROM   UserActions ua
           GROUP BY ua.UserId
       ) AS lastActionDateWithUserId
WHERE  UserActions.UserId = lastActionDateWithUserId.UserId 
  AND  UserActions.ActionDate = lastActionDateWithUserId.lastActionDate

Now, assume that i already have a table structure set up in scalaquery 0.9.5 for the UserActions such as:

case class UserAction(userId:Int,actionDate:Timestamp,description:String)

object UserActions extends BasicTable[UserAction]("UserActions"){

    def userId = column[Int]("UserId")

    def actionDate = column[Timestamp]("ActionDate")

    def description  = column[String]("Description")

    def * = userId ~ actionDate ~ description <> (UserAction, UserAction.unapply _)
}

My question is: in ScalaQuery/SLICK how can i perform such a query?.

Answer 1

I have used Slick 1.0.0 with Scala 2.10.

I defined the objects like this:

case class UserAction(userId: Int, actionDate: Timestamp, description: String)

object UserActions extends Table[UserAction]("UserActions") {

  def userId = column[Int]("UserId")
  def actionDate = column[Timestamp]("ActionDate")
  def description = column[String]("Description")
  def * = userId ~ actionDate ~ description <> (UserAction, UserAction.unapply _)
}

Within session block

Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver") withSession {
  //...
}

I inserted some sample data

UserActions.insert(UserAction(10, timeStamp, "Action 1"))
UserActions.insert(UserAction(10, timeStamp, "Action 2"))
UserActions.insert(UserAction(10, timeStamp, "Action 3"))
UserActions.insert(UserAction(20, timeStamp, "Action 1"))
UserActions.insert(UserAction(20, timeStamp, "Action 2"))
UserActions.insert(UserAction(30, timeStamp, "Action 1"))

Query(UserActions).list foreach println

First thing to do is create the max query

// group by userId and select the userId and the max of the actionDate
val maxQuery =
  UserActions
    .groupBy { _.userId }
    .map {
      case (userId, ua) =>
        userId -> ua.map(_.actionDate).max
    }

The resulting query looks like this

val result =
  for {
    ua <- UserActions
    m <- maxQuery
    if (ua.userId === m._1 && ua.actionDate === m._2)
  } yield ua

result.list foreach println