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

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?.

Answers


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

Need Your Help

nagios wont run when I add this

http webserver ping nagios

I want to be able to check if my site is up and monitor it by nagios locally.

Enable/disable vibration function in android?

android service vibrate android-vibration

I have implemented vibration using vibrator .In my application, when the user press the button, vibration works.For some users wont like vibration in app so i had a toggle button as vibration on/of...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.