scala - 根据 ScalaQuery/SLICK 中列的 MAX 值选择行

Question

说我有这样的表:

UserActions
    UserId INT
    ActionDate TIMESTAMP
    Description TEXT

保存用户执行某些操作的日期。如果我想获得每个用户执行的最后一个操作，我将不得不在 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

现在，假设我已经在 scalaquery 0.9.5 中为 UserActions 设置了一个表结构，例如:

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 _)
}

我的问题是:在 ScalaQuery/SLICK 中我如何执行这样的查询？。

Answer 1

我在 Scala 2.10 中使用了 Slick 1.0.0。

我定义了这样的对象:

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 _)
}

在 session 块内

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

我插入了一些示例数据

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

首先要做的是创建最大查询

// 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
    }

结果查询如下所示

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

result.list foreach println