Compilation Error When Filtering on Aggregation Functions

[mhriemers]

Version: 4.8.0 Module: quill-jdbc-zio Database: postgresql

Expected behavior

import io.getquill._

val ctx = new SqlMirrorContext(MirrorSqlDialect, SnakeCase)
import ctx._

case class Person(firstName: String, lastName: String, age: Int)

val q = quote {
  query[Person]
    .groupBy(_.lastName)
    .map { case (lastName, people) =>
      (
        lastName,
        people.filter(_.firstName != "John").size,
        people.filter(_.firstName != "John").map(_.age).sum
      )
    }
}
println(ctx.run(q).string)

// SELECT x1.last_name AS _1, COUNT(*) FILTER (WHERE x1.first_name <> 'John') AS _2, SUM(x1.age) FILTER (WHERE x1.first_name <> 'John') AS _3 FROM person x1 GROUP BY x1.last_name
// or
// SELECT x1.last_name AS _1, COUNT(CASE WHEN x1.first_name <> 'John' THEN 1 END) AS _2, SUM(CASE WHEN x1.first_name <> 'John' THEN x1.age END) AS _3 FROM person x1 GROUP BY x1.last_name

Actual behavior

// error: Query compilation failed. Invalid group by aggregation: 'x1.filter(x2 => x2.firstName != "John").size'

Steps to reproduce the behavior

https://scastie.scala-lang.org/9aFplZTKSQ6KO1WEOC1a9A

Workaround

import io.getquill._

val ctx = new SqlMirrorContext(MirrorSqlDialect, SnakeCase)
import ctx._

case class Person(firstName: String, lastName: String, age: Int)

val countFilter = quote {
  (cond: Query[Boolean]) => sql"COUNT(*) FILTER (WHERE $cond)".pure.as[Long]
}

def sumFilter[N: Numeric] = quote {
  (expr: Query[N], cond: Query[Boolean]) => sql"SUM($expr) FILTER (WHERE $cond)".pure.as[N]
}

val q = quote {
  query[Person]
    .groupBy(_.lastName)
    .map { case (lastName, people) =>
      (
        lastName,
        countFilter(people.map(_.firstName != "John")),
        sumFilter[Int].apply(people.map(_.age), people.map(_.firstName != "John"))
      )
    }
}
println(ctx.run(q).string)

// SELECT x1.last_name AS _1, COUNT(*) FILTER (WHERE x1.first_name <> 'John') AS _2, SUM(x1.age) FILTER (WHERE x1.first_name <> 'John') AS _3 FROM person x1 GROUP BY x1.last_name

@getquill/maintainers

[hsyndg98]

This is my first comment. I'm sorry if I can't be clear. I think the solution might be to first filter and group the remaining people according to their surnames.

val q = quote {
  query[Person]
    .filter(_.firstName != lift("John"))
    .groupBy(_.lastName)
    .map { case (lastName, people) =>
      (lastName,people.size,people.map(_.age).sum)
    }
}
//SELECT x1.last_name, COUNT(*), SUM(x1.age) FROM person x1 WHERE x1.first_name <> ? GROUP BY x1.last_name