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.

score 27 · Accepted Answer · edited Aug 05 '22 at 06:36

27

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.

edited Aug 05 '22 at 06:36

nemoo

answered Feb 04 '15 at 17:00

Ende Neu

1

I thought it would be something like that, but using your code I get: `Type mismatch. Expected (Employees) => Boolean, actual (Employees) => Any` – BAR Feb 04 '15 at 17:02
I can't reproduce your error to be honest, also that error doesn't seem slick related. – Ende Neu Feb 04 '15 at 17:10
I am using direct embedding. Maybe that is why. – BAR Feb 04 '15 at 17:11
1

I get errors using your code with the regular lifted embedding. Are you also on version 2? – BAR Feb 04 '15 at 17:59
Yes I'm on version 2.X but I've never use the direct embedding feature. – Ende Neu Feb 04 '15 at 20:53
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])` – mrvisser Jan 20 '16 at 23:48
4

@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[_]]` – Josh Aug 31 '16 at 18:31
"newer versions of Slick" = 2.1.0, http://slick.lightbend.com/doc/2.1.0/upgrade.html#isnull-and-isnotnull – arve0 May 29 '18 at 10:23

1 Answers1