Why can't I use options inside a transparent request

Question

To save me from having to create so many methods, I tried to pass the option to my method and then check if the parameter is defined, if so, then apply a 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 get errors saying:

 polymorphic expression cannot be instantiated to expected type; IntelliJ errors are expected Boolean actual Column[Boolean]. 

Is this type of offer just not possible in a slick request, or am I just doing it wrong?

Answer 1

I can’t say 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 brackets and that you must yield something different; the return type for query will be Unit .

Answer 2

Of course, I don’t see any problem here, just use a filter (or withFilter) and map the parameters.

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

With a filter, you can go down to Scala for a map or true fallback expressions. If you start with u < users if... then there is no way to use Scala conventions. Calling bind avoids potential malicious input (i.e., if parameters appear outside the application).

Answer 3

Why it does not work

As cvot noted in his comment , the reason for this does not work, because:

Slick translates None as SQL NULL, including SQL NULL with 3-digit logical code, so (None === a) is None, regardless of the value of a ... basically, if there is nothing in the expression. the expression will be None, so the filter expression will be considered false, and the query result will be empty.

However, there is a way to get the same behavior that you want (filtering only if an extra value is provided).

The way to achieve the desired behavior

The main thing to note is that to compile, compile Scala into a combination of calls to map / flatMap / withFilter / filter . Slick, if I understand correctly, works with the resulting structure when it compiles the understanding of Scala into an SQL query.

This allows us to create 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 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 often, we can generalize this filtering pattern to Option to 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 the entire 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 >= _) 

Answer 5

Since the Slick query is converted to SQL, which has no concept of the isDefined and get methods of the Option class. But you can fix this by calling methods outside the query and passing the results (via the map function on the options).

The following code should fix:

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

locationAndSalary may seem strange, but we use it to understand it to use the value only when both locationId and salary have a value and store the result in a tuple, with locationId in the first position and the salary in the second. The following links explain this as follows: Scala: for understanding with options .

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