Slick - Filter Row if Column is Null

Question

How do I filter rows in Slick if a column is null?

val employees = Queryable[Employees]

// Error
val query = employees.filter( _.terminationDate == Nil )

Might be important to note that

terminationDate: Option[String]

I am using Direct Embedding.

Miserere

Answer 1

Newer versions of Slick:

val query = employees.filter(_.terminationDate.isEmpty)

and

val query = employees.filter(_.terminationDate.isDefined)

Older versions of Slick had their own way of checking for null values in a column:

val query = employees.filter(_.terminationDate.isNull)

The opposite was isNotNull.

Turkish

I thought it would be something like that, but using your code I get: Type mismatch. Expected (Employees) => Boolean, actual (Employees) => Any – Miserere

I can't reproduce your error to be honest, also that error doesn't seem slick related. – Turkish

I am using direct embedding. Maybe that is why. – Miserere

I get errors using your code with the regular lifted embedding. Are you also on version 2? – Miserere

Yes I'm on version 2.X but I've never use the direct embedding feature. – Turkish

It seems at least in slick 3.1.1 isEmpty and isDefined no longer exist. The only way I can find to do a null check is something like: .filter(_.terminationDate === Option.empty[String]) – Weksler

@CharlieBrown it should still work on - as long as your column is defined as a column[Option[SomeType]] rather than a column[SomeType]. I think isEmpty and isDefined are only defined on columns of type Rep[Option[_]] – Hygeia

"newer versions of Slick" = 2.1.0, slick.lightbend.com/doc/2.1.0/upgrade.html#isnull-and-isnotnull – Portamento