Group and Aggregate queries, unlike Select queries do not return
arbitrary types, but the types :
Group, Measures and GroupWithMeasures that merely hold tuples.
Squeryl diverges slightly from SQL in that aggregate functions are
not allowed within a select.
They are instead declared in a ‘compute’ clause which is in fact a
select in disguise, since
it’s arguments end up in the generated SQL’s select clause. The
motivation for this design
choice is to make it a bit harder to write invalid Select statements,
since the DSL forces
a ‘compute’ clause to either replace a select or to follow a groupBy.
As the following example illustrates, the types of the resulting tuples
are determined by the
arguments of the groupBy and compute clause.
Aggregate Query Declaration |
Query Type |
from(aTable)(t=> groupBy(t.aString,t.anInt)) |
Query[ Group[(String,Int)]] |
from(aTable)(t=> groupBy(t.aString,t.anIntOption)) |
Query[ Group[(String,Option[Int])]] |
from(aTable)(t=> compute(min(t.aString),max(t.anInt))) |
Query[ Measures[(Option[String],Option[Int])]] |
from(aTable)(t=> groupBy(t.aString,t.anInt) compute(max(t.aString),avg(t.anInt))) |
Query[ GroupWithMeasures[(String,Int),(Option[String],Option[Float])]] |
groupBy and compute clauses are mutually exclusive with the select
clause in a query,
in other words a query uses either select or a combination of groupBy
and compute.
Note how avg(t.anInt) transforms the return type into an
Option[Float].
Rules for type conversions are explained in the Type
Mapping section.
The groupBy argument list is replicated in the SQL statement’s group by
clause
and in the select, the compute argument list is appended to the select
list
in the generated SQL.
Here is an example, the following Squeryl statement :
- Note : countDistinct takes zero to many arguments, zero arguments
translates into ‘count(distinct *)’,
Translates into this SQL statement :
Notice how the groupBy(a.id) causes Artist1.id to be in the
select list and in the group by clause, while compute(count)
puts the aggregate function ‘count’ in the select list.