Play Slick: Like with an Option[String] column

Question

How can I make a query with a like when the object is a column[Option[String]]?

This is my model:

val id: Rep[Int] = column[Int]("id", O.AutoInc, O.PrimaryKey)
val ownerName: Rep[String] = column[String]("owner_name")
val membershipCode: Rep[Option[String]] = column[Option[String]]("membership_code")

And this is my query:

val query = orderTableQuery.filter { order =>
  Seq(
    search.filter(!_.isEmpty).map(filterString => {
      val searchTerm = s"%$filterString%".toLowerCase
      Seq(
//            Some(order.membershipCode.toLowerCase like searchTerm), // This is the one causing problems
        Some(order.ownerName.toLowerCase like searchTerm) // This works fine
      ).flatten.reduceLeftOption(_ || _).getOrElse(true: Rep[Boolean])
    }),
    // ...other optional filters
  ).flatten.reduceLeftOption(_ && _).getOrElse(true: Rep[Boolean])
}

However, when I try to remove the comment tags from that line, the type of the sequence changes, so i can not make the reduceLeftOption(_ || _) part, if I add a get inside the option column then it compile fine, but when i try the query slick throws an error because you can not use a get in the database (which for me makes sense), but then, how can I handle this Option[String] column?

Edit

I have tried some things, like:

Compile fine, but in execution throws exception in the get

Some(order.membershipCode.get.toLowerCase.like(searchTerm))
The reduceLeftOption(_ || _) does not work anymore

Some(order.membershipCode.toLowerCase.like(searchTerm))
Type mismatch

Some(order.membershipCode.toLowerCase.like(Some(searchTerm)))

Edit 2

Now this is fixed, more info in https://github.com/slick/slick/issues/1664

Can you try Some(order.membershipCode.toLowerCase like Some(searchTerm))? — ka4eli
The like method does not accept an Some(String), only string. — rekiem87

Richard Dallaway Richard Dallaway · Accepted Answer · 2017-07-31T07:31:47

You can use asColumnOf to achieve this:

order.membershipCode.asColumnOf[String].toLowerCase like searchTerm

Depending on your database, this will produce a query that contains a WHERE clause that looks something like this:

where lcase(cast("membershipCode" as VARCHAR)) like ...etc

Slick issue 1664 is a ticket looking for a general solution to this.

Play Slick: Like with an Option[String] column

1 Answers