Multiple columns in group by

[David McLaughlin]

Hi,

Is it possible to execute a query which groups on multiple columns in
Squeryl?

E.g.

SELECT "T1"."name", "T1"."id", count(*) AS "count"
FROM "T1", "T2"
WHERE "T1"."id" = "T2"."t1Id"
GROUP BY "T1"."id", "T2"."t1Id"
ORDER BY "count" DESC



The reason I need a query like this is because I have a report to
generate and I need a displayable name (which has a unique constraint,
so this query is safe) along with a numeric id which is used in a
follow-through link. This result set is potentially large and I want
to avoid the n+1 problem if I were to just return the id and then
fetch the display name for each result.

I noticed that GroupWithMeasures accepts only a tuple of type
parameters and I had the idea to replace one of the tuples with a
custom type that contained "name" and "id", but I'm not sure what the
resulting groupBy clause would look like.

If this is not possible, does Squeryl offer any convenience for
falling back to raw SQL?


Thanks,
David

[David McLaughlin]

Sorry, that should be:

GROUP BY "T1"."id", "T1"."name"

[Maxime Lévesque]

val q =
  from(t1s,t2s)((t1,t2) =>
    where(t1.id === t2.t1Id)
    groupBy(t1.name, t1.id, t2.t1Id)
    compute(count)
    orderBy(count)
  )

The type of the query is : GroupWithMeasures[Product3[String,Long,Long],Long]

for(z <- q) {

  the GroupWithMeasure is composed of :

   z.key._1 //name
   z.key._2 // t1.id
   z.key._3 // t2.t1Id
   z.measures // the count(*)
}

the "key" is a tuple (ProductN) that holds the N expressions of the groupBy
and the measures holds the M agregate expressions of the compute function.
The exception is when N or M == 1, the value is placed directly instead of
being a Product1.

So you have :

 GroupWithMeasures[Product3[String,Long,Long],Long]

instead of :

 GroupWithMeasures[Product3[String,Long,Long],Product1[Long]]

ML

[David McLaughlin]

Great, thanks!