How to use SQL "LIKE" operator in SLICK

Question

Maybe a silly question. But I have not found an answer so far. So how do you represent the SQL's "LIKE" operator in SLICK?

Solution 1

Exactly as you normally would!

val query = for {
  coffee <- Coffees if coffee.name like "%expresso%"
} yield (coffee.name, coffee.price)

Will generate SQL like

SELECT name, price FROM coffees WHERE NAME like '%expresso%';

Solution 2

This is how I got it to work:

// argMap is map of type [Str, Str]
val query = for {
    coffee <- coffees if (
      argMap.map{ case (k,v) =>
        metric.column[String](k) like s"%${v}%"
      }.reduce(_ && _)
    )
  } yield(coffee.name)

And then you can run this using your db: val res = db.run(query.result)

Of course res is a future here that you need to use await to get the actual result.

Comments

[wassertim]

Thanks. Is it scala query or slick? For me in slick it only works with coffe.name.like("%expresso%") (with dot separator).

[kiritsuku]

@Tim: Your syntax is equivalent to the one Faiz presented (note the dot between coffee and name).

[wassertim]

No it does not sschaef. Faiz's answer has no dot between name and like - so I get a compiler error. If I separate name and like with dot - no error. Why's that? Am I supposed to import some namespace?

[Faiz]

Well if you're using say, mysql, import scala.slick.driver.MySQLDriver.simple._ will help. replace MySQLDriver with the driver of your choice.

[wassertim]

Does make sense. import slick.driver.ExtendedProfile works fine

[Travis Stevens]

Does anyone know if like does sanitation on the string?

[Dileep]

Just to be aware : this solution allows sql injection!

[Tim Gautier]

if coffee.name like "%${queryStringFromUser}%" <-- This seems to sanitize the string. If it contains \ or ' it will be escaped.