Scalaquery：过滤“任何”条件的组合 [英] Scalaquery: filter by “any”-combination of conditions

问题描述

我想用或加入任意长度的过滤器列表。如果这个列表是固定长度的，那么看起来像这样：

  query.filter（filters（0）|| filters （1）|| ... || filter（n））
  

使用和会很容易：
$ b $ pre $ for（filter←filters）
query = query .filter（过滤器）


将评估结果连接到 Boolean 或也很简单：

= evaluateToBools.foldLeft（true）（
（left：Boolean，right：Eval2Bool）=>
left || right.evaluate
）

---

更新：

，如果scalaquery的 filter 是一个标准的话，那很容易。

所以我的具体问题是：
如果我有一组字符串元组：

  val tms =设置（（A，a），（B，b）。 ..）
  

以及包含t和m两列的查询，

如何生成一个表示以下SQL的过滤器：

  WHERE / AND（（t =A和m =a）或（t =B和m =b）或...）
  pre> 
 
 
 ...或者可以在运算符中使用sql 这样的元组吗？
  ... WHERE（t，m）IN（（A，a），（B，b），... ）
  
如果是这样，如何在scalaquery中执行
 
 
 
 
 
 Hack：
 
 
 
目前，我做了以下操作： 
 
 
  val tms = markers map {tm⇒tm._1 +||+ tm._2} 
 query.filter（d⇒d._4 ++ ||++ d._5 inSet tms）
   
 
 ...但这是无法忍受的hacky。
 
 
解决方案
 
 
 
我实现了Stefan的解决方案：
 
 $ $ $ $ $ $ c $ r $ rq = rq过滤器{d⇒
标记图{tm⇒
（d._4是tm._1）&& （d._5是tm._2）
} reduceLeft {_ || _} 
} 
  
 
 
 Query.filter 没有什么特别之处，它可以使组合谓词过滤Scala集合。是的，它的确有一个更复杂的类型：
 
 $ $ $ $ $ $ c $ def filter [T]（f：E => T）（implicit wt：CanBeQueryCondition [T]）：Query [E，U] = ... 
  

安全地忽略 CanBeQueryCondition 类型类别并假定 T 为 Column [Boolean] 或 Column [Option [Boolean]] ，只要您对所有谓词使用相同的类型（您始终可以这样做）。 b
$ b

那么你的过滤器序列的类型是什么？我认为这是你的问题所在。让我们开始筛选一个Scala集合 List [User] 。这里谓词的类型应该是 User =>布尔，你可以使用 || 减少应用的谓词来合并它们：

<$ p用户（1，foo），$ b $（$ b $） b用户（2，bar），
用户（3，blub）
）

val filters = List（
{u：User => ; u.id == 1}，
{u：User => u.name ==bar}
）

val filtered = users filter {u = >
过滤器map {_（u）} reduceLeft {_ || _





现在我们为这些添加一个数据库表， User objects：

  class DBUsers extends Table [User]（USERS）{
 def id = column [Int]（ID）
 def name = column [String]（NAME）
 def * = id〜name< （User，User.unapply _）
} 
对象DBUsers扩展DBUsers 
  

过滤查询[DBUsers] 需要谓词类型 DBUsers => Column [布尔值] ：

  val dbFilters = List（
 {u：DBUsers = > u.id === 1}，
 {u：DBUsers => u.name ===bar} 
）
  pre> 
 
 
组合和应用过滤器与以前完全一样： 
 
 
  val dbFiltered = DBUsers filter {u => 
 dbFilters map {_（u）} reduceLeft {_ || _ 
 
 $ / code> 
关于 inSet 元组的方法：我认为这是一个好主意。请提交一个改进请求。一些数据库系统可以本地支持它，而其他数据库系统可以使用这个答案中概述的编码。
 
I want join an arbitrary-length list of filters with or. If the list would be fixed-length, it would look like this:
query.filter(filters(0) || filters(1) || … || filter(n))
Joining filters with and would be easy:
for (filter ← filters)
    query = query.filter(filter)
Joining things that evaluate to Booleans with or is easy, too:
val any = evaluateToBools.foldLeft(true)(
    (left: Boolean, right: Eval2Bool) =>
    left || right.evaluate
)




Update:

as i wrote it, it would be easy, if scalaquery’s filter was a standard one. unfortunately, scalaquery only allows these filters to be executed by the sql engine.

so my specific question would be:
if i have a set of string tuples:
val tms = Set( ("A","a"), ("B", "b"), ... )
and a query with the two columns "t" and "m",

how can i generate a filter that represents the following SQL:
... WHERE/AND ( (t="A" and m="a") or (t="B" and m="b") or ... )
…or can sql in operators be used with tuples like this?
... WHERE (t,m) IN (("A","a"), ("B","b"), ...)
and if so, how to do it in scalaquery



Hack:

currently, i do the following:
val tms = markers map { tm ⇒ tm._1 +"||"+ tm._2 }
query.filter(d ⇒ d._4 ++"||"++ d._5 inSet tms)
…but that’s unbearably hacky.

Solution

I implemented Stefan’s solution like this:
rq = rq filter { d ⇒
    markers map { tm ⇒
        (d._4 is tm._1) && (d._5 is tm._2)
    } reduceLeft { _||_ }
}

解决方案
There is really nothing about Query.filter which would make this any different than combining predicates for filtering a Scala collection. Yes, it does have a more complicated type:
def filter[T](f: E => T)(implicit wt: CanBeQueryCondition[T]): Query[E, U] = ...
But you can safely ignore the CanBeQueryCondition typeclass and assume T to be Column[Boolean] or Column[Option[Boolean]] as long as you use that same type for all of your predicates (which you can always do).

So what is the type of your filters sequence? I assume this is where your problem lies. Let's start with filtering a Scala collection List[User]. Here the predicates should have the type User => Boolean and you can reduce the applied predicates with || to combine them:
case class User(id: Int, name: String)

val users = List(
  User(1, "foo"),
  User(2, "bar"),
  User(3, "blub")
)

val filters = List(
  { u: User => u.id == 1 },
  { u: User => u.name == "bar" }
)

val filtered = users filter { u =>
  filters map { _(u) } reduceLeft { _ || _ }
}
Now we add a database table for these User objects:
class DBUsers extends Table[User]("USERS") {
  def id = column[Int]("ID")
  def name = column[String]("NAME")
  def * = id ~ name <> (User, User.unapply _)
}
object DBUsers extends DBUsers
Filtering a Query[DBUsers] requires predicates of type DBUsers => Column[Boolean]:
val dbFilters = List(
  { u: DBUsers => u.id === 1 },
  { u: DBUsers => u.name === "bar" }
)
Combining and applying the filters is exactly the same as before:
val dbFiltered = DBUsers filter { u =>
  dbFilters map { _(u) } reduceLeft { _ || _ }
}
Regarding an inSet method for tuples: I think it's a good idea. Please file an enhancement request for it. Some database systems could support it natively, and for the others the encoding outlined in this answer could be used.

                        
这篇关于Scalaquery：过滤“任何”条件的组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！