scala – Slick：动态创建查询连词/析取

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

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的类型

不幸的是,有3种不同的类型具有CanBeQueryCondition并将它们放入列表中以折叠成单个谓词似乎并不容易(即过滤器很容易应用,但&&和||运算符很难应用(据我所知)).但幸运的是,似乎我们可以轻松地将布尔值转换为Colunm [布尔]到列[Option [Boolean]].扩展方法.

所以让我们定义我们的谓词类型：

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

折叠谓词列表(即使用连词/分离,即组成AND和OR子句)

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

// 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: 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] = {
    if ( predicates.isEmpty ) matchNone[Item,predicate2) => table: T =>
        predicate1(table) || predicate2(table)
      }
    }
  }

动态过滤案例类

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

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谓词的定义

使用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)

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