Picking max item by column from group by in Slick 3.x

Question

I'm trying to write a Slick query to find the "max" element within a group and then continue querying based on that result, however I'm getting a massive error when I try what I thought was the obvious way:

val articlesByUniqueLink = for {
  (link, groupedArticles) <- historicArticles.groupBy(_.link)
  latestPerLink <- groupedArticles.sortBy(_.pubDate.desc).take(1)
} yield latestPerLink

Since this doesn't seem to work, I'm wondering if there's some other way to find the "latest" element out of "groupedArticles" above, assuming these come from an Articles table with a pubDate Timestamp and a link that can be duplicated. I'm effectively looking for HAVING articles.pub_date = max(articles.pub_date).

The other equivalent way to express it yields the same result:

val articlesByUniqueLink = for {
  (link, groupedArticles) <- historicArticles.groupBy(_.link)
  latestPerLink <- groupedArticles.filter(_.pubDate === groupedArticles.map(_.pubDate).max.get)
} yield latestPerLink

[SlickTreeException: Unreachable reference to s2 after resolving monadic joins + 50 lines of Slick node trees.

Which exact version of Slick are you using? Did you take a look at these bug reports? https://github.com/slick/slick/issues/1316, https://github.com/slick/slick/pull/1513 — devkat, Jul 18 '16 at 06:50
Having a similar issue, did you ever find an answer to this? — JoshSGman, Jul 10 '17 at 23:34

score 2 · Answer 1 · answered Aug 20 '18 at 05:58

The best way I found to get max or min or etc. per group in Slick is to use self join on grouping result:

val articlesByUniqueLink = for {
  (article, _) <- historicArticles join historicArticles.groupBy(_.link)
      .map({case (link, group) => (link, group.map(_.pubDate).max)})
          on ((article, tuple) => article.link === tuple._1 && 
              article.pubDate === tuple._2)
} yield article

If there is possible to produce duplicates with on condition, just drop duplicates like this after.

Picking max item by column from group by in Slick 3.x

1 Answers1