How can I do aggregate queries in Slick?

Question

For example, I have the following table definitions:

object Houses extends Table[Long]("Houses") {
  def id = column[Long]("id")
  def * = id
}
object Rooms extends Table[(Long, Long)]("Rooms") {
  def id = column[Long]("id")
  def houseId = column[Long]("houseId")
  def size = column[Int]("size")
  def * = id ~ houseId ~ size
}

And I want to select the biggest room for each house.

I came up with the following trick:

val query = {
  (r1, r2) <- Rooms leftJoin Rooms on ((r1,r2) =>
    r1.houseId === r2.houseId && r1.size > r2.size
  )
  if r2.id.isNull
} yield r1

It does what I need, but is ugly, totally unreadable, and seems to hurt performance. I tried to use groupBy on query, but seems I am misunderstanding some core concept - I can't get the types right.

Is there a better way to do such aggregate query in Slick?

Answer 1

First, this kind of query is not totally simple in plain SQL. Slick groupBy translates to SQL GROUP BY in the end, so to use it we need a SQL query with GROUP BY

One such query could look like

SELECT r2.* FROM 
  (SELECT r.houseId, MAX(size) as size FROM Rooms r GROUP BY r.houseId) mx
  INNER JOIN
  Rooms r2 on r2.size = mx.size and r2.houseId = mx.houseId

This can now be translated to slick

val innerQuery = Query(Rooms).groupBy(_.houseId).map {
  case (houseId, rows) => (houseId, rows.map(_.size).max)
}

val query = for {
  (hid, max) <- innerQuery
  r <- Rooms if r.houseId === hid && r.size === max
} yield r

However I had problems with aggregated queries used in other queries in current version of slick.

But the query can be written without a GROUP BY using EXISTS

SELECT r.* FROM Rooms r 
  WHERE NOT EXISTS (
    SELECT r2.id FROM Rooms r2 WHERE r2.size > r.size and r2.houseId = r.houseId)

This can again be translated to slick

val query = for {
  r <- Rooms
  if !Query(Rooms).filter(_.houseId === r.houseId).filter(_.size > r.size).exists
} yield r

Another option would probably be use of window functions, but I can't really help you with those and I don't think slick can work with them.

(Note that I don't have a scala compiler at hand so there may be errors in the code)