Slick:如何将 SQL LIKE 语句与 SQL IN 语句结合使用 [英] Slick: How can I combine a SQL LIKE statement with a SQL IN statement

问题描述

我基本上想用更光滑"的东西替换以下代码:

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]

因此生成的 SQL 将是:

So the resulting SQL would be:

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

因此，此 dbAction 将返回一个操作，我可以使用该操作来查询列表中包含某些名称部分的所有用户.

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

所以

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

将返回查询所有包含字符串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"))

但找不到结合它的方法

另一种可能解决的方法是通过正则表达式.有没有办法实现类似于以下 SQL 语句的内容:

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]*';

由于这在某种程度上是一个如何使用正则表达式的不同问题，因此我为此创建了一个不同的问题:Slick:有没有办法使用正则表达式创建 WHERE 子句?

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?

推荐答案

查看您发布的代码，我认为您不需要将 IN 与 字面结合喜欢.我读这个问题是想要做一个正则表达式查询.尽管 Slick 不支持开箱即用的 ~* 运算符，但您可以自己添加它.这将为您提供一种使用提升嵌入样式的 Slick 查询来执行查询的方法.

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.

为此，您可以使用 SimpleExpression 构建器.关于它的文档不多，但起点是 标量数据库函数 参考手册页.

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
}

为了获得 regexLike，我们可以丰富(增强，pimp")一个字符串列来拥有 regexLike 方法:

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)
  }
}

隐式类部分允许编译器在任何时候有一个Rep[String]调用一个方法来构造RegexLikeOps类Rep[String] 还没有(即，当要求 regexLike 时).

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

我们的 regexLike 方法采用另一个 Rep[String] 参数作为模式，然后使用 SimpleExpression 构建器安全地构建我们想要的 SQL使用.

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) )

生成的 SQL(在我的 H2 测试中)是:

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

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

完整代码为:https://github.com/d6y/so46199828

这篇关于Slick:如何将 SQL LIKE 语句与 SQL IN 语句结合使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！