scala - 在 Scala ActiveRecord 中使用 groupby 编写连接查询

Question

我正在尝试在scala Active record中编写特定查询。但它总是什么都不返回。我已经阅读了 github 页面上的 wiki，但它没有包含很多信息。我要写的查询是

SELECT e.name, e.id, COUNT(pt.pass_id) as pass_count, e.start_date, e.total_passes_to_offer
FROM events e inner join passes p on e.id = p.event_id inner join pass_tickets pt on p.id = pt.pass_id where e.partner_id = 198 group by e.name, e.id

我试过的是

Event.joins[Pass, PassTicket](
                (event, pass, passTicket) => (event.id === pass.eventId, pass.id === passTicket.passId)
            ).where(
                (event, _, _) => event.partnerId === partnerId
            ).select(
                (event, pass, _) => (event.name, event.id, PassTicket.where(_.passId === pass.id).count, event.startDate, event.totalPassesToOffer)
            ).groupBy( data => data._2)

但首先，返回类型变成了一个映射，而不是一个列表。其次，在执行时，即使数据存在，它也不会返回任何内容。直接对数据库运行 SQL 查询时，会返回预期的结果。

标签： scalajoinactiverecordgroup-by

Answer 1

scala-activerecorddsl 基于squeryl，所以当计算一个复杂的查询时，我们可以下拉到 squeryl 级别并使用它的statement工具来漂亮地打印 SQL 语句。这样，我们可以迭代地对 dsl 进行 tweek 处理，直到获得所需的 SQL 语句。例如，假设我们有以下架构：

object Tables extends ActiveRecordTables {
  val persons = table[Person]
  val tickets = table[Ticket]
}

case class Person(name: String, email: String, age: Int) extends ActiveRecord
case class Ticket(price: Float, priority: Boolean) extends ActiveRecord {
  lazy val person = belongsTo[Person]
}

object Person extends ActiveRecordCompanion[Person]
object Ticket extends ActiveRecordCompanion[Ticket]

然后我们放到 squeryl dsl 来定义以下查询

  val query =
    dsl.join(Person.toQuery, Ticket.toQuery)((person, ticket) =>
      groupBy(person.name, person.age)
      compute(count(ticket.id))
      on(person.id === ticket.id)
    )

然后我们可以使用漂亮的打印语句

println(Person.inTransaction(query.statement))

输出实际的 SQL 语句

Select
  q1.people6_name as g0,
  q1.people6_age as g1,
  count(q7.tickets11_id) as c0
From
(Select
   people6.name as people6_name,
   people6.email as people6_email,
   people6.age as people6_age,
   people6.id as people6_id
 From
   people people6
)  q1
 inner join (Select
   tickets11.priority as tickets11_priority,
   tickets11.price as tickets11_price,
   tickets11.id as tickets11_id
 From
   tickets tickets11
)  as q7 on (q1.people6_id = q7.tickets11_id)
Group By
  q1.people6_name,
  q1.people6_age

一旦我们在 squeryl 中找出了正确的 dsl，那么我们至少知道这是可能的，然后我们也可以尝试在 scala-activerecord 中编写它。这种方法的潜在优势是 squerly 似乎有更多的文档。请注意它在Group 和 Aggregate Queries上的声明，这也间接适用于 scala-activerecord：

Squeryl 与 SQL 略有不同，因为select 中不允许使用聚合函数。相反，它们在“计算”子句中声明，这实际上是变相的选择，因为它的参数最终出现在生成的 SQL 的选择子句中。这种设计选择的动机是让编写无效的 Select 语句更加困难，因为DSL 强制“计算”子句替换 select 或跟随 groupBy。

根据我的理解，这意味着我们不应该PassTicket.where(_.passId === pass.id).count在select子句中写。

关于groupBy返回 a Map，我们可以调用values.toList它来获取列表，例如，说我们有

  Person("Picard", "[e-mail]", 34).save
  Person("Data", "[e-mail]", 40).save
  Person("Geordi", "[e-mail]", 40).save

那么println(Person.groupBy(person => person.age).values.toList)应该给

List(
  List(Person(Data,[e-mail],40), Person(Geordi,[e-mail],40)), 
  List(Person(Picard,[e-mail],34))
)