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?

score 49 · Accepted Answer · answered Feb 05 '13 at 06:05

49

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%';

answered Feb 05 '13 at 06:05

Faiz

16,025
5
48
37

1

Thanks. Is it scala query or slick? For me in slick it only works with coffe.name.like("%expresso%") (with dot separator). – wassertim Feb 05 '13 at 06:24
@Tim: Your syntax is equivalent to the one Faiz presented (note the dot between `coffee` and `name`). – kiritsuku Feb 05 '13 at 08:54
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? – wassertim Feb 05 '13 at 09:12
Well if you're using say, mysql, `import scala.slick.driver.MySQLDriver.simple._` will help. replace `MySQLDriver` with the driver of your choice. – Faiz Feb 05 '13 at 10:09
Does make sense. import slick.driver.ExtendedProfile works fine – wassertim Feb 05 '13 at 12:19
4

Does anyone know if like does sanitation on the string? – Travis Stevens Jun 02 '13 at 05:55
Just to be aware : this solution allows sql injection! – Dileep Jun 02 '15 at 17:11
3

if coffee.name like "%${queryStringFromUser}%" <-- This seems to sanitize the string. If it contains \ or ' it will be escaped. – Tim Gautier Jun 03 '15 at 20:54

score 3 · Answer 2 · edited Dec 18 '19 at 04:33

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.

score 0 · Answer 3 · answered May 06 '21 at 18:20

Suppose you have a table named, logs with 3 fields -

id
message
datetime

You want to perform LIKE operation. So it will be:

 def data(data: ReqData): Future[Seq[Syslog]] =  {
      sysLogTable
        .filter(_.datetime >= data.datetimeFrom)
        .filter(_.datetime <= data.datetimeUntil)
        .filter(_.message like s"%${data.phrase}%")
        .result
 }

Note: for sysLogTable

val sysLogTable: TableQuery[SyslogsTable] = TableQuery[SyslogsTable]

class SyslogsTable(tag: Tag) extends Table[Syslog](tag, "logs") {
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

  def message = column[String]("message")

  def datetime = column[Timestamp]("date")

  def * = (id.?, message, datetime) <> ((Syslog.apply _).tupled, Syslog.unapply)
}

Note: for Syslog case class

case class Syslog(
   id: Option[Long],
   message: String,
   datetime: Timestamp
)

How to use SQL "LIKE" operator in SLICK

3 Answers3

Linked