Do Aggregation with Slick

Question

My database structure looks like this:

id | content

I what to get the entry with max id (not just id).

I read the answer How to make aggregations with slick, but I found there is no first method in the statement: Query(Coffees.map(_.price).max).first. How to do that now?

What if I need the content of the item with the max id?

Answer 1

To retrieve another column, you could do something like the following. The below example calculates the max of one column, finds the row with that maximum value, and returns the value of another column in that row:

val coffees = TableQuery[Coffees]

val mostExpensiveCoffeeQuery =
  for {
    maxPrice <- coffees.map(_.price).max.result
    c <- maxPrice match {
      case Some(p) => coffees.filter(_.price === p).result
      case None => DBIO.successful(Seq())
    }
  } yield c.headOption.map(_.name)

val mostExpensiveCoffee = db.run(mostExpensiveCoffeeQuery)
// Future[Option[String]]

Alternatively, to return a full Coffees object:

val mostExpensiveCoffeeQuery =
  for {
    ...
  } yield c.headOption

val mostExpensiveCoffee = db.run(mostExpensiveCoffeeQuery)
// Future[Option[Coffees]]