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

score 5 · Answer 1 · answered Sep 05 '19 at 10:51

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

Nagarjuna Pamu · Answer 2 · 2017-10-12T10:59:58.010

2

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))

edited Oct 12 '17 at 10:59

answered Oct 12 '17 at 10:03

Nagarjuna Pamu

14,737
3
22
40

This doesn't work in the scenario where rtbFromClient is None, in that case no filtering should be performed - see the else LiteralColumn(true) in the original question – Rory Oct 12 '17 at 10:28
@Rory Simple change can help, when none is present. edited the ans – Nagarjuna Pamu Oct 12 '17 at 10:32
What if I have multiple optional filters though? I can't chain them this way - filter(...).filter(...) – Rory Oct 12 '17 at 10:35
@Rory `query` returns a query so, after that you can do chaining with `map`, `filter` etc – Nagarjuna Pamu Oct 12 '17 at 10:37
@Rory use this `userTable.filter(row => optional.map(_ === row.rtbFromClient).getOrElse(row.rtbFromClient.isNull))`. this is much better – Nagarjuna Pamu Oct 12 '17 at 10:39
What is 'optional'? – Rory Oct 12 '17 at 10:43
Also, there is no isNull function on row. rtbFromClient - it is a Rep[Option[Boolean]] – Rory Oct 12 '17 at 10:45
@Rory replace `isNull` with `isEmpty` . added details in answer. – Nagarjuna Pamu Oct 12 '17 at 10:48
@Rory clarified in answer – Nagarjuna Pamu Oct 12 '17 at 10:49
This doesn't compile, says 'cannot find symbol ===': userTable.filter(row => rtbFromClient.map(_ === row.rtb).getOrElse(row.rtb.isEmpty)) – Rory Oct 12 '17 at 10:52
@Rory use this `userTable.filter(row => rtbFromClient.map(row.rtb === _).getOrElse(row.rtb.isEmpty))` ... Just shifted the `_` to right. Becoz `===` is defined on Rep – Nagarjuna Pamu Oct 12 '17 at 10:55
Doesn't compile: Type slick.lifted.Rep[_ >: Option[Boolean] with Boolean] cannot be a query condition (only Boolean, Rep[Boolean] and Rep[Option[Boolean]] are allowed [error] val query = advertiserTable.filter(row => rtb.map(row.rtb === _).getOrElse(row.rtb.isEmpty)) – Rory Oct 12 '17 at 10:58
@Rory try this `userTable.filter(row => rtbFromClient.map(value => row.rtb === Some(value): Option[Boolean]).getOrElse(row.rtb.isEmpty))` – Nagarjuna Pamu Oct 12 '17 at 11:00
@Rory this will definitely work. `userTable.filter(row => rtbFromClient.map(value => row.rtb === Some(value): Option[Boolean]).getOrElse(row.rtb.isEmpty))` – Nagarjuna Pamu Oct 12 '17 at 11:01
This doesn't compile: type mismatch; [error] found : Some[Boolean] [error] required: slick.lifted.Rep[?] – Rory Oct 12 '17 at 11:07
@Rory Please paste the compiler error in the comments section – Nagarjuna Pamu Oct 12 '17 at 11:13
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/156550/discussion-between-pamu-and-rory). – Nagarjuna Pamu Oct 12 '17 at 11:32

score 0 · Accepted Answer · answered Oct 13 '17 at 13:11

var query = userTable.drop(page.skip).take(page.top)

if (rtbFromClient.isDefined) {
    query = query.filter(row => row.rtb.isDefined && row.rtb === rtbFromClient)
}

// More if blocks with optional filters here
...

dbProvider.db.run(query.result)

score 0 · Answer 4 · answered Feb 20 '19 at 10:05

0

try this

val query = userTable.filter(row => rtbFromClient.map(x => Option(x) === row.rtb).getOrElse(row.rtb.isEmpty.?))

answered Feb 20 '19 at 10:05

Forzaken

21
4

Can you explain that further such that others can learn from it? – Nico Haase Feb 20 '19 at 10:48
it's a direct answer to the asked question. At least it works for me well (i had the same problem) – Forzaken Feb 20 '19 at 14:53
Well, as you posted this as an answer, I guess that's what it should be :) But please add some explanation to that code: what does it do? Keep in mind that people come here to learn from such answers, not just to copy&paste them – Nico Haase Feb 20 '19 at 14:57

score 0 · Answer 5 · answered May 31 '19 at 15:15

I was facing the same issue. After playing around I realised one can filter by the option directly, so if we have the column

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

and we're trying to filter by

rtbFromClient: Option[Boolean] = ...

then we can do:

val query: DBIO[Seq[TableElementType]] = 
  if (rtbFromClient.isDefined)
    userTable.filter(_.rtb === rtbFromClient).result
  else
    userTable.result

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

5 Answers5