光滑的动态分组 [英] Slick dynamic groupby

问题描述

我有这样的代码:

def query(buckets: List[String]): Future[Seq[(List[Option[String]], Option[Double])]] = {
    database.run {
        groupBy(row => buckets.map(bucket => customBucketer(row.metadata, bucket)))
            .map { grouping =>
                val bucket = grouping._1
                val group = grouping._2
                (bucket, group.map(_.value).avg)
            }
            .result
    }
}

private def customBucketer(metadata: Rep[Option[String]], bucket: String): Rep[Option[String]] = {
    ...
}

我希望能够以光滑的方式创建查询，从而使groupby并在给定的列列表中进行收集.

I am wanting to be able to create queries in slick which groupby and collect on a given list of columns.

编译时遇到的错误是:

[error] Slick does not know how to map the given types.
[error] Possible causes: T in Table[T] does not match your * projection,
[error]  you use an unsupported type in a Query (e.g. scala List),
[error]  or you forgot to import a driver api into scope.
[error]   Required level: slick.lifted.FlatShapeLevel
[error]      Source type: List[slick.lifted.Rep[Option[String]]]
[error]    Unpacked type: T
[error]      Packed type: G
[error]                         groupBy(row => buckets.map(bucket => customBucketer(row.metadata, bucket)))
[error]                                ^

推荐答案

这是Slick 3.2.3(以及我的方法的一些背景知识)的解决方法:

Here's a workaround for Slick 3.2.3 (and some background on my approach):

您可能已经注意到动态地选择列很容易，只要您可以采用固定类型，例如: columnNames = List("col1", "col2") tableQuery.map( r => columnNames.map(name => r.column[String](name)) )

You may have noticed dynamically selecting columns is easy as long as you can assume a fixed type, e.g: columnNames = List("col1", "col2") tableQuery.map( r => columnNames.map(name => r.column[String](name)) )

但是，如果您通过groupBy操作尝试类似的方法，Slick将抱怨它"does not know how to map the given types".

But if you try a similar approach with a groupBy operation, Slick will complain that it "does not know how to map the given types".

因此，尽管这并不是一个很好的解决方案，但您可以通过静态定义两者至少满足Slick的类型安全性:

So, while this is hardly an elegant solution, you can at least satisfy Slick's type-safety by statically defining both:

1. groupby列类型
2. groupBy列数量的上限/下限

1. groupby column type
2. Upper/lower bound on the quantity of groupBy columns

实现这两个约束的一种简单方法是再次采用固定类型，并为所有可能的groupBy列数量分支代码.

A simple way of implementing these two constraints is to again assume a fixed type and to branch the code for all possible quantities of groupBy columns.

这是完整的Scala REPL会话，可为您提供一个想法:

Here's the full working Scala REPL session to give you an idea:

import java.io.File

import akka.actor.ActorSystem
import com.typesafe.config.ConfigFactory
import slick.jdbc.H2Profile.api._

import scala.concurrent.{Await, Future}
import scala.concurrent.duration._


val confPath = getClass.getResource("/application.conf")
val config = ConfigFactory.parseFile(new File(confPath.getPath)).resolve()
val db = Database.forConfig("slick.db", config)

implicit val system = ActorSystem("testSystem")
implicit val executionContext = system.dispatcher

case class AnyData(a: String, b: String)
case class GroupByFields(a: Option[String], b: Option[String])

class AnyTable(tag: Tag) extends Table[AnyData](tag, "macro"){
  def a = column[String]("a")
  def b = column[String]("b")
  def * = (a, b) <> ((AnyData.apply _).tupled, AnyData.unapply)
}

val table = TableQuery[AnyTable]

def groupByDynamically(groupBys: Seq[String]): DBIO[Seq[GroupByFields]] = {
  // ensures columns are returned in the right order
  def selectGroups(g: Map[String, Rep[Option[String]]]) = {
    (g.getOrElse("a", Rep.None[String]), g.getOrElse("b", Rep.None[String])).mapTo[GroupByFields]
  }

  val grouped = if (groupBys.lengthCompare(2) == 0) {
    table
      .groupBy( cols => (cols.column[String](groupBys(0)), cols.column[String](groupBys(1))) )
      .map{ case (groups, _) => selectGroups(Map(groupBys(0) -> Rep.Some(groups._1), groupBys(1) -> Rep.Some(groups._2))) }
  }
  else {
    // there should always be at least one group by specified
    table
      .groupBy(cols => cols.column[String](groupBys.head))
      .map{ case (groups, _) => selectGroups(Map(groupBys.head -> Rep.Some(groups))) }
  }

  grouped.result
}

val actions = for {
  _ <- table.schema.create
  _ <- table.map(a => (a.column[String]("a"), a.column[String]("b"))) += ("a1", "b1")
  _ <- table.map(a => (a.column[String]("a"), a.column[String]("b"))) += ("a2", "b2")
  _ <- table.map(a => (a.column[String]("a"), a.column[String]("b"))) += ("a2", "b3")
  queryResult <- groupByDynamically(Seq("b", "a"))
} yield queryResult

val result: Future[Seq[GroupByFields]] = db.run(actions.transactionally)
result.foreach(println)

Await.ready(result, Duration.Inf)

当您可以拥有多于几个groupBy列时(例如，为10多个案例使用单独的if分支将变得单调)，这变得很丑陋.希望有人会加入并编辑此答案，以了解如何将该样板隐藏在语法糖或抽象层的后面.

Where this gets ugly is when you can have upwards of a few groupBy columns (i.e. having a separate if branch for 10+ cases would get monotonous). Hopefully someone will swoop in and edit this answer for how to hide that boilerplate behind some syntactic sugar or abstraction layer.

这篇关于光滑的动态分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！