slick-pg
slick-pg copied to clipboard
tminglei
Aggregations in pg-slick are not compatible with groupBy
The aggregation functions in pg-slick are unfortunately not compatible with groupBy statements from Slick >3.0. In our code-base I've added a work-around, that might need to be introduced in pg-slick as well. The following code was added to PgAggFuncSupportSuite:
import slick.ast.Library.SqlAggregateFunction
import slick.ast.{BaseTypedType, TypedType}
import slick.lifted.FunctionSymbolExtensionMethods
test("group by aggregates") {
import FunctionSymbolExtensionMethods._
val ArrayAgg = new SqlAggregateFunction("array_agg")
implicit def singleColumnQueryExtensionMethods[B1 : BaseTypedType, C[_]](q: Query[Rep[B1], _, C]) = new Object {
def arrayAgg(implicit tm: TypedType[List[B1]]): Rep[List[B1]] = ArrayAgg.column[List[B1]](q.toNode)
}
val sql1 = tabs.groupBy(t => t.name).map {
case (name, tabs) => tabs.map(t => t.x).arrayAgg
}
println(sql1.result.statements.head)
}
Do we want these and other SqlAggregateFunctions to be added to PgAggFuncSupport?
Hmm ... I'm doing a test in PgAggFuncSupportSuite.scala using codes:
test("used with groupBy") {
import agg.PgAggFuncSupport.GeneralAggFunctions._
implicit def shapedValueFromQuery[E, U, C[_]](q: Query[E, U, C]): E = q.shaped.value
val sql = tabs.groupBy(_.bool).map {
case (bool, group) => (bool, arrayAgg(group.name.?))
}.result.statements.head
println(s"sql: $sql")
}
If it works, that will be great, since we needn't duplicate these methods.
But I encountered exception:
No type for symbol col2 found in Vector[@t2<{x: Double', count: Int', y: Double', bool: Boolean', col2: String'}>]
slick.SlickException: No type for symbol col2 found in Vector[@t2<{x: Double', count: Int', y: Double', bool: Boolean', col2: String'}>]
at slick.ast.Type$class.select(Type.scala:24)
at slick.ast.CollectionType.select(Type.scala:132)
at slick.ast.Select.buildType(Node.scala:530)
at slick.ast.SimplyTypedNode$class.withInferredType(Node.scala:124)
at slick.ast.Select.withInferredType(Node.scala:520)
at slick.ast.Select.withInferredType(Node.scala:520)
at slick.ast.Node$class.infer(Node.scala:90)
at slick.ast.Select.infer(Node.scala:520)
...
Any good idea?
Well, I realized I tried a wrong way. I finally resolved above problem, but then I encountered more problems. Because they are differently treated in slick, and difficult to merge w/o bad side effect.
So I'm going to turn to your way.
@gedejong Can you send me the PR, or you leave me take care of it?
@tminglei I'd love to make a PR, since I think it's important to contribute back to OSS. I would implement the functions val ArrayAgg = new SqlAggregateFunction("array_agg") and others, which might not be optimal, since it duplicates the other code. I was really hoping there would be another way and tried a couple of different approaches as well... Seeing that you could not find a more elegant approach as well, I will make a PR with the complete set of extra aggregations, starting somewhere next week. Unfortunately, I cannot start earlier because of time limitations.
@gedejong do you have time now? If you're still busy, I'm going to move it forward.
@tminglei I looked at the problem quite a lot (two evenings) and thought of different approaches as well. The problem is obviously the hard-coded case matching in slick itself (on Apply). I feel bad to ruin your nice design by placing all these extra (similar and incompatible) aggregates next to it (plus, it still won't fix the statistical aggregates). Unfortunately, I'm still quite busy this week (couple of hectic releases). Please move it forward if you have time.
Hmm... it seems slick didn't support aggregates with other parameters in groupBy. Because of this, the statistical aggregates can't be supported by slick built-in logic.
We need find a way out.
@tminglei
Because of this, the statistical aggregates can't be supported by slick built-in logic.
Does this mean, general purpose aggregate functions work (arrayAgg specifically) with groupBy?
If so, do you mind providing simple example?
@danishin no, these slick-pg added aggregate functions (e.g. arrayAgg) can't work with groupBy unless I resolved the issue.
@tminglei Has there been any update on this issue? I'm trying to learn how to use arrayAgg() at the moment but having some difficulties
@SergeyZharikhin No. In fact, I didn't find a good idea to move it forward.
@tminglei , so arrayAgg in PgAggFuncSupport.GeneralAggFunctions isn't working as expected?
In case it helps anyone reading or working on this issue, I hacked together an arrayAgg extension method for a client project on top of PgArraySupport I can't guarantee the code is 100% correct but it seems to be working for me:
import com.github.tminglei.slickpg.{ExPostgresProfile, PgArraySupport}
object PostgresDriver extends ExPostgresProfile with PgArraySupport {
import slick.ast._
import slick.ast.Library._
import slick.lifted.FunctionSymbolExtensionMethods._
override val api = MyAPI
object MyAPI extends API with ArrayImplicits {
// Declare the name of an aggregate function:
val ArrayAgg = new SqlAggregateFunction("array_agg")
// Implement the aggregate function as an extension method:
implicit class ArrayAggColumnQueryExtensionMethods[P, C[_]](val q: Query[Rep[P], _, C]) {
def arrayAgg[B](implicit tm: TypedType[List[B]]) =
ArrayAgg.column[List[B]](q.toNode)
}
}
}
object UseCase {
import PostgresDriver.api._
class UserRoleTable(tag: Tag) extends Table[(Long, String)](tag, "user_roles") {
val userId = column[Long]("user_id", O.PrimaryKey)
val role = column[String]("role")
def * = (userId, role)
}
lazy val UserRoleTable = TableQuery[UserRoleTable]
def selectAllRoles: DBIO[Seq[(Long, Seq[String])]] =
UserRoleTable
.groupBy(row => row.userId)
.map { case (id, query) => (id, query.map(_.role).arrayAgg) }
.result
}
The code is adapted from https://github.com/slick/slick/blob/master/slick/src/main/scala/slick/lifted/ExtensionMethods.scala#L156-L173.
Can this idea be generalized for binary aggregate functions such as jsonb_object_agg?
Does anybody know how to generalize the method for Rep[Option[_]]? Currently I get NPE if there's actually None inside.
