CODEWITHSUNDEEP
scalaplayframeworkslick
rekiem87
rekiem87

Reputation: 1573

Play Slick: Like with an Option[String] column

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:

  1. Compile fine, but in execution throws exception in the get

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

  2. The reduceLeftOption(_ || _) does not work anymore

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

  3. 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

Upvotes: 1

Views: 1302

Answers (1)

Richard Dallaway
Richard Dallaway

Reputation: 4320

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.

Upvotes: 3

Related Questions