Slick 3.0.0 Aggregate Queries

Question

I'm trying to do a complex querying on two tables. They look like below:

Table1:

  id:
  name:
  table2Id:
  version:

Table2:

  id:
  comments:

Assuming that I have the appropriate Slick classes that represents these tables, I'm trying to get all the elements from Table1 and Table 2 that satisfies the following condition:

Table1.table2Id === Table2.id and max(Table1.version)

I tried the following:

val groupById = (for {
  elem1 <- table1Elems
  elem2 <- table2Elems if elem1.id === elem2.id
} yield (elem1, elem2)).groupBy(_._1.id)

I know that I have to map the groupById and look for the max version, but I'm not getting the syntax right! Any help?

What I need is effectively Slick equivalent of the following SQL query:

SELECT * 
FROM t t1 
WHERE t1.rev = (SELECT MAX(rev) FROM t t2 WHERE t2.id = t1.id)

virtualeyes virtualeyes · Accepted Answer · 2015-05-23T19:56:43

To simulate:

SELECT * from t t1 WHERE t1.rev = (SELECT max(rev) FROM t t2 WHERE t2.id = t1.id)

Try either of the following:

val q1 = for{
  t < table1 if t.rev === (
    table2.filter(_.id === t.id).map(_.rev).max
  )
} yield t

val q2 = table1.filter{t=> t.rev === (
  table2.filter(_.id === t.id).map(_.rev).max
)}.map(identity)

EDIT based on comment:

val q = (for{
  t1 < table1; t2 <- table2 if t1.id === t2.id
} yield t1).orderBy(_.version.max).reverse.take(1)

Slick 3.0.0 Aggregate Queries

1 Answers