ZIO quill groupByMap on query with join

Question

groupByMap in ZIO Quill is throwing an exception when trying to group by on a query with a join.

I'm using scala 3.2.0 and quill 4.6.0.

What's the proper way to use group by on a join query in zio quill?

case class Post(id: Long, title: String)
case class Comment(id: Long, message: String, postId: Long)

def getPostCommentsCount(): Task[List[PostCommentCounts]] =
    run {
      query[Post]
        .join(query[Comment])
        .on(_.id == _.postId)
        .groupByMap((p, c) => p.id)((p, c) =>
          PostCommentCounts(
            p.id,
            count(c.id.toInt),
          ),
        )
    }
      .provideEnvironment(ZEnvironment(dataSource))

I'm getting the following error.

[error]  37 |    run {
[error]     |    ^
[error]     |Query splicing failed due to error: scala.MatchError: querySchema("Post").join(querySchema("Comment")).on((x3, x4) => x3.id == x4.postId).groupByMap(x$1 => x$1._1.id)(x$1 => Post(id: x$1._1.id, title: x$1._1.title, message: x$1._1.message, locationId: x$1._1.locationId, createdAt: x$1._1.createdAt)) (of class io.getquill.ast.GroupByMap)
[error]     |   at io.getquill.norm.capture.AvoidAliasConflict.apply$$anonfun$1(AvoidAliasConflict.scala:203)
[error]     |   at io.getquill.util.Interpolator$Traceable.andReturnIf(Interpolator.scala:193)
[error]     |   at io.getquill.norm.capture.AvoidAliasConflict.apply(AvoidAliasConflict.scala:205)
[error]     |   at io.getquill.ast.StatefulTransformer.apply(StatefulTransformer.scala:9)
[error]     |   at io.getquill.ast.StatefulTransformer.apply$(StatefulTransformer.scala:3)
[error]     |   at io.getquill.norm.capture.AvoidAliasConflict.apply(AvoidAliasConflict.scala:43)
[error]     |   at io.getquill.ast.StatefulTransformer.apply(StatefulTransformer.scala:153)
[error]     |   at io.getquill.ast.StatefulTransformer.apply$(StatefulTransformer.scala:3)
[error]     |   at io.getquill.norm.capture.AvoidAliasConflict.recurseAndApply$$anonfun$1(AvoidAliasConflict.scala:81)
[error]     |   at io.getquill.util.Interpolator$Traceable.andReturnIf(Interpolator.scala:193)
[error]     |   at io.getquill.norm.capture.AvoidAliasConflict.recurseAndApply(AvoidAliasConflict.scala:92)
[error]     |   at io.getquill.norm.capture.AvoidAliasConflict.apply$$anonfun$1(AvoidAliasConflict.scala:145)
[error]     |   at io.getquill.util.Interpolator$Traceable.andReturnIf(Interpolator.scala:193)
[error]     |   at io.getquill.norm.capture.AvoidAliasConflict.apply(AvoidAliasConflict.scala:205)
[error]     |   at io.getquill.norm.capture.AvoidAliasConflictApply.apply(AvoidAliasConflict.scala:287)
[error]     |   at io.getquill.norm.Normalize.apply$$anonfun$1(Normalize.scala:37)
[error]     |   at io.getquill.util.Interpolator$Traceable.andReturn(Interpolator.scala:172)
[error]     |   at io.getquill.norm.Normalize.apply(Normalize.scala:37)
[error]     |   at io.getquill.ast.StatelessTransformer.apply(StatelessTransformer.scala:9)
[error]     |   at io.getquill.ast.StatelessTransformer.apply$(StatelessTransformer.scala:3)
[error]     |   at io.getquill.norm.Normalize.apply(Normalize.scala:30)
[error]     |   at io.getquill.context.sql.norm.SqlNormalize.$init$$$anonfun$5(SqlNormalize.scala:40)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at scala.Function1.$anonfun$andThen$1(Function1.scala:85)
[error]     |   at io.getquill.context.sql.norm.SqlNormalize.apply(SqlNormalize.scala:66)
[error]     |   at io.getquill.context.sql.norm.SqlNormalize$.apply(SqlNormalize.scala:13)
[error]     |   at io.getquill.context.sql.idiom.SqlIdiom.normalizeAst(SqlIdiom.scala:40)
[error]     |   at io.getquill.context.sql.idiom.SqlIdiom.normalizeAst$(SqlIdiom.scala:24)
[error]     |   at io.getquill.PostgresDialect$.normalizeAst(PostgresDialect.scala:196)
[error]     |   at io.getquill.context.sql.idiom.SqlIdiom.doTranslate(SqlIdiom.scala:60)
[error]     |   at io.getquill.context.sql.idiom.SqlIdiom.translate(SqlIdiom.scala:98)
[error]     |   at io.getquill.context.sql.idiom.SqlIdiom.translate$(SqlIdiom.scala:24)
[error]     |   at io.getquill.PostgresDialect$.translate(PostgresDialect.scala:196)
[error]     |   at io.getquill.context.StaticTranslationMacro$.processAst(StaticTranslationMacro.scala:75)
[error]     |   at io.getquill.context.StaticTranslationMacro$.$anonfun$11$$anonfun$3(StaticTranslationMacro.scala:260)
[error]     |   at scala.Option$WithFilter.flatMap(Option.scala:283)
[error]     |   at io.getquill.context.StaticTranslationMacro$.$anonfun$11(StaticTranslationMacro.scala:276)
[error]     |   at scala.Option$WithFilter.flatMap(Option.scala:283)
[error]     |   at io.getquill.context.StaticTranslationMacro$.apply(StaticTranslationMacro.scala:276)
[error]     |   at io.getquill.context.QueryExecution$RunQuery.applyQuery$$anonfun$2(QueryExecution.scala:235)
[error]     |   at scala.util.Try$.apply(Try.scala:210)
[error]     |   at io.getquill.context.QueryExecution$RunQuery.applyQuery(QueryExecution.scala:235)
[error]     |   at io.getquill.context.QueryExecution$RunQuery.apply(QueryExecution.scala:198)
[error]     |   at io.getquill.context.QueryExecution$.applyImpl(QueryExecution.scala:395)

Answer 1

At first I thought it's because PostCommentCounts cannot be translated to SQL query, but as I read the quill's documentation, I saw that it's completely fine. There's one thing in the stacktrace that caught my attention:

x$1 => Post(id: x$1._1.id, title: x$1._1.title, message: x$1._1.message, locationId: x$1._1.locationId, createdAt: x$1._1.createdAt)

This is the map function in groupByMap. These fields: title: x$1._1.title, message: x$1._1.message, locationId: x$1._1.locationId, createdAt: x$1._1.createdAt (with x$1 referring to (p, c)) are neither in group by or aggregated. Is it because you wanted to summarize your question or is this the actual query you're running?