Aggregate multiple columns without groupBy in Slick 2.0

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?

Answers


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


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.


Need Your Help

AJAX Response Text Empty

javascript html ajax dom

I'm creating a system that interacts with Minecraft server using AJAX. My JavaScript code is as follows:

VBA Excel Code Running Much slower in 2010 than 2007

vba excel-2007 excel-2010

I have some code that runs fine in Excel 2007 but when used in Excel 2010 takes about ten times longer to run and causes the whole taskbar/other programs to be unresponsive.