How to filter on a Option[Boolean] column in slick

I had the same issue. My solution is (tested with Slick 3.3.x):

val query = usersTable.filterOpt(rtbFromClient)(_.rtb === _)

Situation 1 (when rtbFromClient is empty) corresponds the following SQL:

select * from users;

Situation 2 (rtbFromClient is defined):

select * from users where rtb = ?;

Fist check if the column is null and then go ahead with another comparison. Ensure that rtbFromClient is not an option.

val query = userTable.filter(row => !row.rtb.isEmpty &&
                                    row.rtb === rtbFromClient)

The first condition ensures that nulls are filtered and the second condition checks the value in case the column value is not null.

In case you have optional value, then below code helps.

def query(value: Option[Boolean]) = value match {
   case Some(userGivenRtbFromClient) =>
    userTable.filter(row => !row.rtbFromClient.isNull &&
         row.rtbFromClient === userGivenRtbFromClient)
   case None => userTable.filter(row => row.rtbFromClient.isNull)
}

The Much cleaner version is here.

rtbFromClient: Option[Boolean] 

rtbFromClient User given optional value to compare with slick column.

userTable.filter(row => rtbFromClient.map(value => row.rtb === Some(value): Option[Boolean]).getOrElse(row.rtb.isEmpty))