Like clause to accept null in slick

Question

val query = for {
 s <- Status if s.code like "%"
} yield (s)

The query above wouldn't return records where Status.code would be null like the way it is in SQL. Is there a way to fetch records with null values when just the "%" wildcard is used with the like clause?

Answer 1

It's a SQL-thing, you need to use "column is null" to match null values. I think you'll need something like this:

val query = for {
 s <- Status if s.code like "%" || s.code.isEmpty
} yield (s)

This will of course match anything, making it quite useless ;-)

Update

Is it something like this you're after:

val all = LiteralColumn(1) === LiteralColumn(1)
val query = for {
 s <- Status if filterString.isEmpty all else s.code like s"%$filterString%"
} yield (s)

Here I make sure that I return some query no matter which branch I enter. It's a little hacky and I'd wished Slick had some built-in shortcut, but I've not been able to find it. It's reads quite well though.