How to use IN clause in Slick?

Question

I have a query which is written is using slick, it is not a plain slick query. The query is a select query which fetches the records from a table called Employee. The results are of type Employee class.

Now there is a list of Strings

val nameFilter= List("Sachin","Naveen"")

and this "nameFilter" comes dynamically and it may have any number of names

var result= dbHandle.db.run((query.drop(10).take(10)).result

The variable query is just a select query for the Employee table which selects a range of records from 11 to 20.

Now I need to filter the records which have names mentioned in the 'nameFilter' and then select the records from 11 to 20. That means I need a query with 'IN' clause.

Please note that this is not a plain Slick SQL query, I have to frame a query in the above format.

Answer 1

You can do this with the method .inSet (see here):

Slick

Slick queries are composable. Subqueries can be simply composed, where the types work out, just like any other Scala code.

val address_ids = addresses.filter(_.city === "New York City").map(_.id)
people.filter(_.id in address_ids).result // <- run as one query

The method .in expects a sub query. For an in-memory Scala collection, the method .inSet can be used instead.

So that would mean for your code:

val nameFilter= List("Sachin","Naveen")
val filteredQuery = query.filter(_.name.inSet(nameFilter))
var result= dbHandle.db.run((filteredQuery.drop(10).take(10)).result

Depending on the source of that input you should consider using .inSetBind to escape the input (see this SO post).