Aggregate multiple columns without groupBy in Slick 2.0

3
votes

I would like to perform an aggregation with Slick that executes SQL like the following:

SELECT MIN(a), MAX(a) FROM table_a;

where table_a has an INT column a

In Slick given the table definition:

class A(tag: Tag) extends Table[Int](tag, "table_a") { 
  def a = column[Int]("a")
  def * = a 
}
val A = TableQuery[A]
val as = A.map(_.a)

It seems like I have 2 options:

  1. Write something like: Query(as.min, as.max)

  2. Write something like:

    as
  .groupBy(_ => 1)
  .map { case (_, as) => (as.map(identity).min, as.map(identity).max) }

However, the generated sql is not good in either case. In 1, there are two separate sub-selects generated, which is like writing two separate queries. In 2, the following is generated:

select min(x2."a"), max(x2."a") from "table_a" x2 group by 1

However, this syntax is not correct for Postgres (it groups by the first column value, which is invalid in this case). Indeed AFAIK it is not possible to group by a constant value in Postgres, except by omitting the group by clause.

Is there a way to cause Slick to emit a single query with both aggregates without the GROUP BY?

2
sqlscalaslick

2 Answers

2
votes

The syntax error is a bug. I created a ticket: https://github.com/slick/slick/issues/630 The subqueries are a limitation of Slick's SQL compiler currently producing non-optimal code in this case. We are working on improving the situation.

As a workaround, here is a pattern to swap out the generated SQL under the hood and leave everything else intact: https://gist.github.com/cvogt/8054159

0
votes

I use the following trick in SQL Server, and it seems to work in Postgres:

select min(x2."a"), max(x2."a")
from "table_a" x2
group by (case when x2.a = x2.a then 1 else 1 end);

The use of the variable in the group by expression tricks the compiler into thinking that there could be more than one group.