groupBy method throws an error in Slick

Question

The codes looks like this:

case class Supplier(snum: String, sname: String, status: Int, city: String)

class Suppliers(tag: Tag) extends Table[Supplier](tag, "suppliers") {
  def snum  = column[String]("snum")
  def sname = column[String]("sname")
  def status   = column[Int]("status")
  def city     = column[String]("city")
  def * = (snum, sname, status, city) <> (Supplier.tupled, Supplier.unapply _)
}

val suppliers = TableQuery[Suppliers]

val gr=suppliers.groupBy(_.city).map{ case (k,v) => (k, v) }.buildColl[Set]

When I compile it, it complains:

Error:(69, 43) No matching Shape found.
Slick does not know how to map the given types.
Possible causes: T in Table[T] does not match your * projection. Or you use an unsupported type in a Query (e.g. scala List).
  Required level: scala.slick.lifted.FlatShapeLevel
     Source type: (scala.slick.lifted.Column[String], scala.slick.lifted.Query[A$A113.this.Suppliers,A$A113.this.Supplier,[+A]Seq[A]])
   Unpacked type: T
     Packed type: G
lazy val gr=suppliers.groupBy(_.city).map{ case (k,v) => (k, v) }.buildColl[Set]
                      ^
Error:(69, 43) not enough arguments for method map: (implicit shape: scala.slick.lifted.Shape[_ <: scala.slick.lifted.FlatShapeLevel, (scala.slick.lifted.Column[String], scala.slick.lifted.Query[A$A113.this.Suppliers,A$A113.this.Suppliers#TableElementType,Seq]), T, G])scala.slick.lifted.Query[G,T,Seq].
Unspecified value parameter shape.
lazy val gr=suppliers.groupBy(_.city).map{ case (k,v) => (k, v) }.buildColl[Set]
                      ^

But if I change case(k,v)=>(k,v) to case(k,v)=>(k,v.length), it works again.

Does anyone have ideas about this?

Answer 1

The reason is: Scala's groupBy returns a Map[..., Seq[...]], in other words a collection containing other collections. A nested collection! But SQL does not support nested collections, it always returns flat tables. Supporting nested collections would require a more sophisticated translation from Scala to SQL than Slick currently does. So instead Slick prohibits this case and requires you to make it flat. case(k,v)=>(k,v.length) does that for example, it turns the type into Map[..., Int]. Slick tells you that by saying Required level: scala.slick.lifted.FlatShapeLevel.

A workaround is doing the grouping on the client, e.g. suppliers.run.groupBy(_.city) or in Slick 2.2 and later db.run(suppliers).groupBy(_.city). In case of a join it can be more efficient to run two queries and join them locally instead of transferring a cartesian product and grouping afterwards.