Squeryl: how to make case insensitive search for optional properties?

Question

I got stuck with the following problem.

I have search method which works fine:

override def search(code: Option[String], 
                title: Option[String], 
                employmentStatusCd: Option[Int], 
                employerId: Int) = MDB {
    from(table)((p) =>
      where(
        (p.code like code.map(v => s"%$v%").?)
          and (p.title like title.map(v => s"%$v%").?)
          and (p.employmentStatusCd === employmentStatusCd.?)
          and (p.employerId === employerId))
        .select(p)
    ).toList
}

How can I improve it to make search processing case insensitive? I know that I can use lower(...), for example:

(lower(pos.title) like lower(s"%$value%"))

But in this example property 'title' is not optional. How to combine optional property

(title.map(v => s"%$v%").?)

with lower(...) to make search case insensitive?

I get an exception if I write next:

lower(title.map(v => s"%$v%").?)

Is there a reason you want to use a db server function to make `title` lowercase? If not, have you tried something like `title.map{ v=> s"%${v.toLowercase}%"}`? — jcern, Dec 04 '14 at 13:32
I have record(position.title = "VP of Dev") in DB. I want to find it typing "vp" or "dev". — Yuriy Balakhonov, Dec 04 '14 at 13:51
Right, but since the string is constant - why not just make it lowercase before passing it in? Something like: `lower(pos.title) like title.map{ v=> s"%${v.toLowercase}%"}`. — jcern, Dec 04 '14 at 13:57

score 0 · Accepted Answer · answered Dec 04 '14 at 15:00

Thanks to jcern for clarification.

The correct method is:

override def search(code: Option[String], 
            title: Option[String], 
            employmentStatusCd: Option[Int], 
            employerId: Int) = MDB {
    from(table)((p) =>
      where(
        (lower(p.code) like code.map(v => s"%${v.toLowercase}%").?)
          and (lower(p.title) like title.map(v => s"%${v.toLowercase}%").?)
          and (p.employmentStatusCd === employmentStatusCd.?)
          and (p.employerId === employerId))
        .select(p)
    ).toList
}

We don't need to use server function to make constant string lowercase.

Squeryl: how to make case insensitive search for optional properties?

1 Answers1