Why can't I use Options inside of a slick query

Question

In order to save me having to create so many methods, I tried passing in Option's into my method and then checking if the Option is defined, if so, then apply the filter.

def getUsers(locationId: Option[Int], companyId: Int, salary: Option[Int]): List[User] = {
  val query = for {
    u <- users if u.companyId === companyId && (locationId.isDefined && u.locationId === locationId.get) && (salary.isDefined && u.salary >= salary.get)

  }
  query.list()
}

I am getting errors saying:

polymorphic expression cannot be instantiated to expected type;

IntelliJ errors are expected Boolean actual Column[Boolean].

Is this type of clause just not possible in a slick query or I'm just doing it wrong?

Answer 1

I can't tell you why but this compiles for me:

def getUsers(locationId: Option[Int], companyId: Int, salary: Option[Int]): List[User] = {
  val query = for {
    u <- users if u.companyId === companyId && locationId.isDefined && u.locationId === locationId.get && salary.isDefined && u.salary >= salary.get
  } yield(u)
  query.list()
}

Note that there are no parenthesis and that you have to yield something otherwise the return type for query would be Unit.

Answer 2

Sure, don't see any issue here, just use filter (or withFilter) and map over the options.

def getUsers(locationId: Option[Int], companyId: Int, salary: Option[Int]): List[User] = (for {
  u <- users filter(u=>
    if (u.companyId === companyId.bind) && 
    (locationId.map(_.bind === u.locationId).getOrElse(true)) && 
    (salary.map(_.bind <= u.salary).getOrElse(true))
  )
} yield u).list()

Using filter allows you to drop down to Scala for the map or true fallback expressions. If you start with u < users if... then there's no way to use Scala conditionals. The bind calls just escape potential malicious input (i.e. if params are coming from outside the application).

Answer 3

Why it doesn't work

As cvot has noted in his comment, the reason this doesn't work is because:

Slick translates the None as SQL NULL including SQLs 3-valued-logic NULL propagation, so (None === a) is None regardless of the value of a ... basically if anything is None in the expression, the whole expression will be None, so the filter expression will be treated as false and the query result will be empty.

That said, there is a way to get the same behavior you want (filtering only if an optional value is provided).

A way to arrive at the desired behavior

The key thing to note is that for comprehensions get compiled down by Scala to a combination of map / flatMap / withFilter / filter calls. Slick, if I understand it correctly, works with the resulting structure when it compiles the Scala comprehension into a SQL query.

This lets us build up a query in parts:

val baseQuery = for {
  u <- users if u.companyId === companyId
} yield u

val possiblyFilteredByLocation = if (locationId.isDefined) {
  baseQuery.withFilter(u => u.locationId === locationId.get
} else baseQuery

val possiblyFilteredBySalaryAndOrLocation = if (salary.isDefined) {
  possiblyFilteredByLocation.withFilter(u => u.salary >= salary.get)
} else possiblyFilteredByLocation

possiblyFilteredBySalaryAndOrLocation.list()

We can simplify this by using a var and fold:

var query = for {
  u <- users if u.companyId === companyId
} yield u
query = locationId.fold(query)(id => query.withFilter(u => u.locationId === id))
query = salary.fold(query)(salary => query.withFilter(u => u.salary >= salary))
query.list()

If we do this frequently, we can generalize this pattern of filtering on an Option into something like this:

// Untested, probably does not compile
implicit class ConditionalFilter(query: Query) {
  def ifPresent[T](value: Option[T], predicate: (Query, T) => Query) = {
    value.fold(query)(predicate(query, _))
  }
}

Then we can simplify our whole filter chain to:

query
  .ifPresent[Int](locationId, (q, id) => q.withFilter(u => u.locationId === id))
  .ifPresent[Int](salary, (q, s) => q.withFilter(u => u.salary >= s))
  .list()

Answer 4

You can use the following solution (with Slick 3.3.x):

def getUsers(locationId: Option[Int], companyId: Int, minSalary: Option[Int]) = 
  users.
    .filter(_.company === companyId)
    .filterOpt(locationId)(_.locationId === _)
    .filterOpt(minSalary)(_.salary >= _)