Scalaquery: filter by “any”-combination of conditions

Question

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 { _||_ }
}

Answer 1

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.