Slick 3.1 - 检索列子集作为案例类 [英] Slick 3.1 - Retrieving subset of columns as a case class

问题描述

我正在使用 Slick 3.1.1，问题是在某些情况下，我想省略一些相当重的列，但仍然将该列的子集具体化为案例类.

I'm working with Slick 3.1.1 and the problem is that in some cases I want to omit some columns that are fairly heavy and still materialize that subset of columns as a case class.

考虑下表定义:

class AuditResultTable(tag: Tag) extends Table[AuditResult](tag, AuditResultTableName) {
    def auditResultId: Rep[Long] = column[Long]("AuditResultId", O.PrimaryKey, O.AutoInc)
    def processorId: Rep[Long] = column[Long]("ProcessorId")
    def dispatchedTimestamp: Rep[Timestamp] = column[Timestamp]("DispatchedTimestamp", O.SqlType("timestamp(2)"))
    def SystemAOutput: Rep[Array[Byte]] = column[Array[Byte]]("SystemAOutput", O.SqlType("LONGBLOB"))
    def SystemBOutput: Rep[Array[Byte]]  = column[Array[Byte]]("SystemBOutput", O.SqlType("LONGBLOB"))
    def isSuccessful: Rep[Boolean] = column[Boolean]("IsSuccessful")


def * : ProvenShape[AuditResult] = (processorId, dispatchedTimestamp, systemAOutput, systemBOutput, isSuccessful, auditResultId) <>
  (AuditResult.tupled, AuditResult.unapply) 

}  

val auditResults = TableQuery[AuditResultTable]  

对应的case类:

case class AuditResult (
   ProcessorId: Long,
   DispatchedTimestamp: Timestamp,
   SystemAOutput: Array[Byte],
   SystemBOutput: Array[Byte],
   IsSuccessful: Boolean,
   AuditResultId: Long = 0L
 )

最后是数据访问查询:

def getRecentFailedAuditsQuery(): Query[AuditResultTable, AuditResult, Seq] = {
  auditResults.filterNot(r => r.isSuccessful)
}

我已经考虑并研究了在此(过时的)答案和其他人:

I've considered and looked into options presented in this (outdated) answer and others:

• 具有与映射到AuditResult 的轻量版本"的默认投影不同的投影，例如省略这些列的 AuditResultLight - 尽管我尽了最大的努力我做不到让这项工作 - 我觉得这应该是正确的方法 - 一旦我有一个有效"的投影，我仍然收到一个光滑的错误找不到匹配的形状.Slick 不知道如何映射给定的类型"
• 使用抽象的 AuditResultTableBase 类和从它派生的两个类构建类层次结构 - 一个添加重"列，另一个不添加它们，两者都有各自的默认投影和案例类.这很有效，但这种方法似乎是错误的，并且需要对如此简单的事情进行相对较大的代码更改.
• 物化元组而不是 case 类 - 这当然可行，但我希望我的数据访问层是强类型的.

• Having a different projection than the default projection which maps to a "light version of the AuditResult, e.g. AuditResultLight that omits those columns - despite my best effort I couldn't make this work - I feel like this should be the right approach - once I had a "working" projection I still got a Slick error "No matching Shape found. Slick does not know how to map the given types"
• Building a class hierarchy with an abstract AuditResultTableBase class and two classes that derive from it - one that adds the "heavy" columns and one without them, both with their respective default projection and case classes. This works nicely, but the approach seems wrong and requires a relatively large code change for such an easy thing.
• Materializing tuples instead of case classes - this of course would work, but I want my data access layer to be strongly typed.

对于这个问题，Slick 3.1 的惯用/最佳实践是什么?我可以为此使用自定义投影吗?如果可以，对于这个特定的示例/查询，SystemAOutput 和 SystemBOutput 是我想省略的重列，它会是什么样子?

What is the idiomatic / best practice for Slick 3.1 for this problem? Can I use a custom projection for this and if so what would that look like for this particular example / query with SystemAOutput and SystemBOutput being the heavy columns I want to omit?

推荐答案

我遇到了类似的问题！你必须定义形状！在 文档 的帮助下，我设法制作轻量级"案例类工作的方法.

I had a similar problem! You have to define the Shape! With help of the documentation I managed to make the approach with a "light" case class work.

首先定义更简单的类:

case class AuditResultLight(
  ProcessorId: Long,
  DispatchedTimestamp: Timestamp,
  IsSuccessful: Boolean,
  AuditResultId: Long = 0L
)

然后，您需要创建案例类的提升版本:

Then, you need to create a lifted version of the case class:

case class AuditResultLightLifted(
  ProcessorId: Rep[Long],
  DispatchedTimestamp: Rep[Timestamp],
  IsSuccessful: Rep[Boolean],
  AuditResultId: Rep[Long]
)

此外，您需要一个隐式对象(形状)来告诉 slick 如何将一个对象映射到另一个对象:

Also, you need an implicit object (the Shape) to tell slick how to map one into another:

implicit object AuditResultLightShape 
  extends CaseClassShape(AuditResultLightLifted.tupled, AuditResultLight.tupled)

现在，您可以定义一个返回 AuditResultLight 的查询(不完全是投影，但据我了解它的工作原理类似):

Now, you can define a query that returns AuditResultLight (not exactly a projection, but as far as I understand it works similarly):

val auditResultsLight = auditResults.map(r => AuditResultLightLifted(r.ProcessorId, r.DispatchedTimestamp, r.IsSuccessful, r.AuditResultId))  

然后，您可以定义以轻量形式返回失败审核的函数:

Then, you can define the function that returns failed audits in a light form:

def getRecentFailedAuditsQuery(): Query[AuditResultTable, AuditResultLight, Seq] = {
  auditResultsLight.filterNot(r => r.isSuccessful)
}

代码要点:https://gist.github.com/wjur/93712a51d392d1840b7fc42>

A gist with the code: https://gist.github.com/wjur/93712a51d392d181ab7fc2408e4ce48b

代码编译并执行，但就我而言，问题是我的 IDE (IntelliJ) 报告 Query[Nothing, Nothing, scala.Seq] 类型为 auditResultsLight.每当我使用 auditResultsLight 并在查询中引用 AuditResultLight 字段时，都会出现语法错误.但是，因此，最后，我决定使用您建议的第二种方法(带有抽象表的方法).代码量几乎相同，但具有 IDE 支持.

The code compiles and executes, but in my case, the issue is that my IDE (IntelliJ) reports Query[Nothing, Nothing, scala.Seq] type for auditResultsLight. I get syntax errors whenever I use auditResultsLight and refer to a field of AuditResultLight in a query. However, because of that, in the end, I decided to use the second approach you suggested (the one with an abstract table). Almost the same amount of code, but with IDE support.

这篇关于Slick 3.1 - 检索列子集作为案例类的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！