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. 开发者_C百科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.

How about this?

query.filter(filters reduceLeft (_ || _))