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

Question

I have the following column in my db, thats a Boolean, but also accepts NULL, so true, false, and NULL are all valid:

def rtb = column[Option[Boolean]]("rtb")

and have the following optional input from the client that I'd like to filter on:

rtbFromClient: Option[Boolean] = ... 

I have the following (based on this answer on how to do queries in slick: https://stackoverflow.com/a/40888918/5300930):

val query = userTable.
      filter(row => 
          if (rtbFromClient.isDefined) 
              row.rtb.get === rtbFromClient.get 
          else 
              LiteralColumn(true)
      )

but am getting this error when the code runs:

Caught exception while computing default value for Rep[Option[_]].getOrElse -- This cannot be done lazily when the value is needed on the database side

I thought it may be because row.rtb.get was throwing exception on call to get because the value in the db was null, so tried changing it to row.rtb.getOrElse(null) and row.rtb.getOrElse(None) but neither of these worked either)

Also tried the following:

if (rtbFromClient.isDefined) {
    val query = query.filter(_.rtb.isDefined).filter(_.rtb.get === rtbFromClient.get)
}

But this also throws the same error at runtime:

Caught exception while computing default value for Rep[Option[_]].getOrElse -- This cannot be done lazily when the value is needed on the database side

To summarise:

• I have an Option[Boolean] column in my db that can contain true, false or NULL (the actual mysql type is a tinyint(1), which is mapped to a slick Option[Boolean])
• I have an optional filter provided by the user, rtbFromClient that I'd like to filter on if present. If present, this will be either true or false
• I have other optional filters (not shown here) that have similar behavior so I'd like to be able to combine them easily

Answer 1

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 = ?;