Slick:动态创建查询连接/分离 [英] Slick: create query conjunctions/disjunctions dynamically

问题描述

我正在尝试为 Slick 表创建一个类型安全的动态 DSL，但不确定如何实现.

I'm trying to create a typesafe dynamic DSL for a Slick table but not sure how to achieve this.

用户可以通过以 form/json 格式发送过滤器来将过滤器发布到服务器，我需要用所有这些来构建一个 Slick 查询.

Users can post filters to the server by sending filters in form/json format, and I need to build a Slick query with all that.

所以基本上这意味着将表示我的过滤器的 Scala 案例类转换为 Slick 查询.

So basically this means transforming a Scala case class representing my filters to a Slick query.

似乎谓词"可以有 3 种不同的形状.我见过特征 CanBeQueryCondition.我可以折叠这些不同的可能形状吗?

It seems the "predicates" can have 3 different shapes. I've seen the trait CanBeQueryCondition. Can I fold over these different possible shapes?

我见过扩展方法 && 和 || 并且知道这与此有关，但我只是不知道该怎么做.

I've seen the extension methods && and || and know there is something to do with this but I just don't know how to do.

基本上，我有一个采用以下类型的谓词列表:

Basically, I have a list of predicates which takes the following types:

(PatientTable) => Column[Option[Boolean]]

或

(PatientTable) => Column[Boolean]

对我来说，问题是所有具有 CanBeQueryCondition 的所有 3 种不同类型都没有一个单一的超类型，所以我真的不知道如何使用 & 折叠谓词;& 一旦添加到列表中，这些不同形状的谓词采用非常通用的类型 List[(PatientTable) =>;列[_ >: Boolean with Option[Boolean]]].

The problem to me is that there is not a single supertype for all the 3 different types that have a CanBeQueryCondition, so I don't really know how do fold the predicates with && as once added to the list these differently shaped predicate takes a very generic type List[(PatientTable) => Column[_ >: Boolean with Option[Boolean]]].

另外，我不确定在 Slick 中什么可以被视为谓词.一个可组合谓词似乎是 Column[Boolean]，但实际上 filter 方法只接受 (PatientTable) => 类型的参数.列[布尔值]

Also, I'm not sure about what can be considered a predicate in Slick. A composable predicate seems to be Column[Boolean], but actually the filter method only accept parameters of type (PatientTable) => Column[Boolean]

推荐答案

我正在用我最终构建的东西来回答我自己的问题.

I'm answering my own question with what I've finally built.

让我们定义一个简单的案例类和行映射器

case class User(
                    id: String = java.util.UUID.randomUUID().toString,
                    companyScopeId: String,
                    firstName: Option[String] = None,
                    lastName: Option[String] = None
                    ) 


class UserTable(tag: Tag) extends Table[User](tag,"USER") {
  override def id = column[String]("id", O.PrimaryKey)
  def companyScopeId = column[String]("company_scope_id", O.NotNull)
  def firstName = column[Option[String]]("first_name", O.Nullable)
  def lastName = column[Option[String]]("last_name", O.Nullable)

  def * = (id, companyScopeId, firstName, lastName) <>
    (User.tupled,User.unapply)
}

Slick 中谓词的概念

我假设谓词"的概念可以放在 TableQuery.filter 中.但是这种类型相当复杂，因为它是一个函数，它接受一个 Table 并返回一个具有隐式 CanBeQueryCondition

I assume that the notion of "predicate" is what can be put inside TableQuery.filter. But this type is rather complex as it is a function that takes a Table and returns a type that has an implicit CanBeQueryCondition

不幸的是，有 3 种不同的类型具有 CanBeQueryCondition 并且将它们放入一个列表中以折叠成单个谓词似乎并不容易(即 filter 很容易申请，但 && 和 || 运算符很难应用(就我尝试过的而言)).但幸运的是，我们似乎可以使用 轻松地将 Boolean 转换为 Colunm[Boolean] 到 Column[Option[Boolean]]>.? 扩展方法.

Unfornunately for me there are 3 different types that have a CanBeQueryCondition and putting them in a list to be folded into a single predicate seems not easy (ie filter is easy to apply, but the && and || operators are hard to apply (as far as I've tried)). But fortunately it seems we can convert easily a Boolean to a Colunm[Boolean] to a Column[Option[Boolean]] with the .? extension method.

那么让我们定义我们的谓词类型:

So let's define our predicate type:

type TablePredicate[Item, T <: Table[Item]] = T => Column[Option[Boolean]]

折叠谓词列表(即使用连词/析取，即组成 AND 和 OR 子句)

现在我们只有一种类型，所以我们可以轻松地将谓词列表折叠成一个

Now we only have one type so we can easily fold a list of predicates into a single

  // A predicate that never filter the result
  def matchAll[Item, T <: Table[Item]]: TablePredicate[Item,T] = { table: T => LiteralColumn(1) === LiteralColumn(1) }

  // A predicate that always filter the result
  def matchNone[Item, T <: Table[Item]]: TablePredicate[Item,T] = { table: T => LiteralColumn(1) =!= LiteralColumn(1) }

  def conjunction[Item, T <: Table[Item]](predicates: TraversableOnce[TablePredicate[Item, T]]): TablePredicate[Item,T]  = {
    if ( predicates.isEmpty ) matchAll[Item,T]
    else {
      predicates.reduce { (predicate1, predicate2) => table: T =>
        predicate1(table) && predicate2(table)
      }
    }
  }

  def disjunction[Item, T <: Table[Item]](predicates: TraversableOnce[TablePredicate[Item, T]]): TablePredicate[Item,T] = {
    if ( predicates.isEmpty ) matchNone[Item,T]
    else {
      predicates.reduce { (predicate1, predicate2) => table: T =>
        predicate1(table) || predicate2(table)
      }
    }
  }

<小时>

动态过滤案例类

从这些谓词原语中，我们可以开始创建基于 case 类的动态、可组合和类型安全的查询 DSL.

From these predicate primitives we can start creating our dynamic, composable and typesafe query DSL based on a case class.

case class UserFilters(
                           companyScopeIds: Option[Set[String]] = None,
                           firstNames: Option[Set[String]] = None,
                           lastNames: Option[Set[String]] = None
                           ) {

  type UserPredicate = TablePredicate[User,UserTable]


  def withFirstNames(firstNames: Set[String]): UserFilters = this.copy(firstNames = Some(firstNames))
  def withFirstNames(firstNames: String*): UserFilters = withFirstNames(firstNames.toSet)

  def withLastNames(lastNames: Set[String]): UserFilters = this.copy(lastNames = Some(lastNames))
  def withLastNames(lastNames: String*): UserFilters = withLastNames(lastNames.toSet)

  def withCompanyScopeIds(companyScopeIds: Set[String]): UserFilters = this.copy(companyScopeIds = Some(companyScopeIds))
  def withCompanyScopeIds(companyScopeIds: String*): UserFilters = withCompanyScopeIds(companyScopeIds.toSet)


  private def filterByFirstNames(firstNames: Set[String]): UserPredicate = { table: UserTable => table.firstName inSet firstNames }
  private def filterByLastNames(lastNames: Set[String]): UserPredicate = { table: UserTable => table.lastName inSet lastNames }
  private def filterByCompanyScopeIds(companyScopeIds: Set[String]): UserPredicate = { table: UserTable => (table.companyScopeId.? inSet companyScopeIds) }


  def predicate: UserPredicate = {
    // Build the list of predicate options (because filters are actually optional)
    val optionalPredicates: List[Option[UserPredicate]] = List(
      firstNames.map(filterByFirstNames(_)),
      lastNames.map(filterByLastNames(_)),
      companyScopeIds.map(filterByCompanyScopeIds(_))
    )
    // Filter the list to remove None's
    val predicates: List[UserPredicate] = optionalPredicates.flatten
    // By default, create a conjunction (AND) of the predicates of the represented by this case class
    conjunction[User,UserTable](predicates)
  }

}

注意 .? 用于 companyScopeId 字段的用法，该字段允许将非可选列适合我们对 Slick 谓词的定义

Notice the usage of .? for the companyScopeId field which permits to fit a non-optional column to our definition of a Slick predicate

使用 DSL

val Users = TableQuery(new UserTable(_))

val filter1 = UserFilters().withLastNames("lorber","silhol").withFirstName("robert")
val filter2 = UserFilters().withFirstName("sebastien")

val filter = disjunction[User,UserTable](Set(filter1.predicate,filter2.predicate))

val users = Users.filter(filter.predicate).list

// results in 
// ( last_name in ("lorber","silhol") AND first_name in ("robert") ) 
// OR 
// ( first_name in ("sebastien") )

结论

这远非完美，但它是初稿，至少可以给你一些灵感:) 我希望 Slick 能够更容易地构建其他查询 DSL 中非常常见的东西(如 Hibernate/JPA Criteria)API)

This is far from being perfect but is a first draft and at least can give you some inspiration :) I would like Slick to make it easier to build such things that are very common in other query DSL (like Hibernate/JPA Criteria API)

另请参阅此要点以获取最新解决方案

See also this Gist for up-to-date solutions

这篇关于Slick:动态创建查询连接/分离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！