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?

Wood

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.

Moonlighting

it does at a 'true' to my query i.e where (... ... and true) – Wood

Uhm to me compiles without errors, have you removed the parenthesis around the conditions? E.g. instead of if true && (id === thatId) should be if true && id === thatId. In the end the if in the for comprehension desugarize to a lazy variant of filter, namely withFilter and it should compile if you have a true inside, I just tried id. – Moonlighting

yes it compiles fine I'm just saying it inserts a true into the sql query. – Wood

I see, I misread your comment. To be honest I had doubt that this could work but I couldn't run an actual query. Probably the full filter condition is translated to SQL from slick where it doesn't make sense (but it does in Scala). I'm curious about how Slick will translate a None and the successive condition in this situation. You could split the query in small pieces, e.g. do the first filter, then if isDefined is true apply the second and so on, but this will be verbose and miss the point of for comprehensions.This is as far as I can go, hopefully somebody else will give a better answer. – Moonlighting

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, even if a is None, the comparison result is still None, which SQL interprets as false. Same for &&. So 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. – Seedbed

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).

Hound

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()

Loella

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 >= _)

Tubercle

Answer 5

Because the Slick query gets translated into SQL, which has no notion of the isDefined and get methods of the Option class. But you can fix this by calling the methods outside the query and passing the results (via the map function on the options).

The following code should fix it:

def getUsers(locationId: Option[Int], companyId: Int, salary: Option[Int]): List[User] = {
    val locationAndSalary = for {
        locId <- locationId;
        sal <- salary
    } yield (locId, sal)

    locationAndSalary.map(locAndSal => {
        val query = for {
            u <- users if u.companyId === companyId && u.locationId === locAndSal._1 && u.salary >= locAndSal._2)  
        } yield u
        query.list()
    }).getOrElse(List[User]()) //If the locationID or salary is None, return empty list.
}

The locationAndSalary may seem strange, but we are using for comprehensions to give use a value only when both locationId and salary has a value and storing the result in a tuple, with the locationId in the first position and salary at the second. The following links explains it: Scala: for comprehensions with Options.

Edit: According to @Ende Neu answer the code compiles if you add the yield-statement, but I still think my solution is more the "Scala way".

Ygerne

Hi, but this isn't what I am after. I want to ignore the locationId and salary where filters if they are None and just have companyId. Your query is not the same thing as it will return List[User]() if they are None. – Wood