Slick: How can I combine a SQL LIKE statement with a SQL IN statement

Question

I basically would like to replace the following code with something more "slicky":

final case class User(firstName: String, lastName: String)

def dbAction(lastNameParts: Seq[String]): SqlStreamingAction[Vector[User], User, Effect]
implicit val getUserResult =
  GetResult((r: PositionedResult) => {
    val resultSet: ResultSet = r.rs
    User(
      resultSet.getString(1),
      resultSet.getString(2)
    )
  })
val pattern = orgIds.mkString("|")
sql"""SELECT u.first_name, u.last_name
      FROM users u
      WHERE last_name ~* $pattern""".as[User]

So the resulting SQL would be:

SELECT u.first_name, u.last_name
FROM users u
WHERE last_name ~* '%bar|baz%';

So this dbAction will return an action which I can use to Query for all users having some name parts included in a list.

So

dbAction(Seq("bar", "baz"))

will return an action querying for all last names containing the strings "bar" or "baz" (case insensitive). I found a way to query for a single pattern

val query = for {
  user <- users if user.lastName like "%bar%"
} yield (user.firstName, user.lastName)

and I found a way to query for list inclusion

u <- users if u.lastName.inSet(Seq("bar", "baz"))

but could not find a way to combine that

EDIT: Another way to possibly solve that would be via a regular expression. Is there a way to achieve something like the following SQL statement:

select * from users where last_name ~ '[\w]*bar[\w]*|[\w]*baz[\w]*';

Since this is somehow a different question how to use a regex I created a different question for this: Slick: Is there a way to create a WHERE clause with a regex?

Answer 1

Looking at the code you've posted, I didn't think you needed to literaly combine an IN with a LIKE. I read the question as wanting to do a regular expression query. Although Slick doesn't support the ~* operator out of the box, you can add it yourself. That would give you a way to execute the query using the lifted embedded style of Slick query.

To do that, you can use the SimpleExpression builder. There's not much documentation on it, but the jumping off point would be the Scalar Database Functions page of the reference manual.

What we want to do is write a method along these lines:

def find(names: Seq[String]): DBIO[Seq[String]] = {
  val pattern = names.mkString("|")
  users.filter(_.lastName regexLike pattern).map(_.lastName).result
}

To get regexLike we can enrich (enhance, "pimp") a string column to have the regexLike method:

implicit class RegexLikeOps(s: Rep[String]) {
  def regexLike(p: Rep[String]): Rep[Boolean] = {
    val expr = SimpleExpression.binary[String,String,Boolean] { (s, p, qb) =>
      qb.expr(s)
      qb.sqlBuilder += " ~* "
      qb.expr(p)
    }
    expr.apply(s,p)
  }
}

The implicit class part is allow the compiler to construct the RegexLikeOps class anytime it has a Rep[String] that calls a method that Rep[String] doesn't already have (i.e., when regexLike is asked for).

Our regexLike method takes another Rep[String] argument as the pattern, and then uses SimpleExpression builder to safely construct the SQL we want to use.

Putting it all together we can write:

val program = for {
  _ <- users.schema.create
  _ <- users ++= User("foo") :: User("baz") :: User("bar") :: Nil
  result <- find( Seq("baz","bar") )
} yield result

println( Await.result(db.run(program), 2.seconds) )

The SQL generated (in my test with H2) is:

select "last_name" from "app_user" where "last_name" ~* 'baz|bar'

The full code is: https://github.com/d6y/so46199828

Answer 2

Just combine these 2 query conditions:

import slick.lifted.Tag
import slick.jdbc.H2Profile.api._
import scala.concurrent.duration._
import scala.concurrent.Await

object Test {
  final case class User(firstName:String, lastName:String, id:Long = 0l)

  class UserTable(tag: Tag) extends Table[User](tag, "user"){
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
    def firstName = column[String]("first_name")
    def lastName = column[String]("last_name")
    def * = (firstName, lastName, id).mapTo[User]
  }



  def main(args:Array[String]):Unit = {
    val db = Database.forConfig("h2config")
    val users = TableQuery[UserTable]

    val initialData = Seq(
      User("Alex", "Arendar"),
      User("David", "Arora"),
      User("Dude", "Stoecki"),
      User("Alexander", "the Great")
    )

    Await.result(
      db.run(
        users.schema.create  andThen (users ++= initialData)
      ), 3 seconds
    )

    val query1 = for {
      user <- users if user.firstName like "%Alex%"
    } yield (user.firstName, user.lastName)

    println(query1.result.statements.head)
    println(Await.result(db.run(query1.result), 3 seconds))

    val query2 = for {
      user <- users if (user.firstName like "%Alex%") && user.firstName.inSet(Seq("Alex", "David"))
    } yield (user.firstName, user.lastName)

    println(query2.result.statements.head)
    println(Await.result(db.run(query2.result), 3 seconds))

    db.close()
  }

My console output is:

select "first_name", "last_name" from "user" where "first_name" like '%Alex%'
Vector((Alex,Arendar), (Alexander,the Great))
select "first_name", "last_name" from "user" where ("first_name" like '%Alex%') and ("first_name" in ('Alex', 'David'))
Vector((Alex,Arendar))

Process finished with exit code 0

As you can see generated SQL does contain both like and in parts.

I used in-memory H2 database here but I think this shall work with any RDBMS.