Slick query with one to many relationship

Question

I'm using slick 3.2.3 and I'm trying to build a query that returns a Seq[Entity1, Seq[Entity2]] for two entities that have a one to many relationship (for each Entity1 are associated multiple Entity2).

So I have my two entities

case class Entity1(name: String, id: Option[Long] = None)
case class Entity2(entity1Id: Long, name: String, id: Option[Long] = None

with the table definitions (that are generated by slick codegen task)

class entity1Table(_tableTag: Tag) extends profile.api.Table[Entity1](_tableTag, "ENTITY_1") {
   ...
}
lazy val groupTable = new TableQuery(tag => new groupTable(tag))

class entity2Table(_tableTag: Tag) extends profile.api.Table[Entity2](_tableTag, "ENTITY_2") {
   ...
}
lazy val entity2Table = new TableQuery(tag => new entity2Table(tag))

Reading this article I've created a query like this

val q = (for {
  e1 <- entity1Table
  e2 <- entity2Table if e2.entity1Id === e1.id
} yield (e1, e2)).groupBy(_._1) map {
  case (entity1, tuples) => (entity1, tuples.map(_._2))
}

db.run(q.result)

but I get this error at compile time:

Error:(19, 35) 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,
 you use an unsupported type in a Query (e.g. scala List),
 or you forgot to import a driver api into scope.
  Required level: slick.lifted.FlatShapeLevel
     Source type: (my.namespace.models.entity1Table, slick.lifted.Query[my.namespace.models.entity2Table,my.namespace.models.Entity2,[+A]Seq[A]])
   Unpacked type: T
     Packed type: G
    } yield (e1, e2)).groupBy(_._1) map {

I suspect that it cannot map the entity1Table and entity2Table.

How can I fix the error?

Leo C Leo C · Accepted Answer · 2018-10-25T23:25:58

As specified in the Slick doc, groupBy currently doesn't support executing a query with nested values of type Query:

The intermediate query (i.e. the query ended with groupBy() without an aggregate function) contains nested values of type Query. These would turn into nested collections when executing the query, which is not supported at the moment. Therefore it is necessary to flatten the nested queries immediately by aggregating their values (or individual columns)

In other words, your Slick groupBy query must be coupled with an aggregate function equivalent to SQL's count(), sum(), etc. For example, the following query which is equivalent to select count(*) ... group by ... having count(*) > 1 would work:

val query = ( for {
    e1 <- entity1Table
    e2 <- entity2Table if e2.entity1Id === e1.id
  } yield (e1, e2)
).
groupBy(_._1).map{
  case (entity1, group) => (entity1, group.size)
}.
filter(_._2 > 1)

Slick query with one to many relationship

1 Answers