Slick:有没有办法用正则表达式创建 WHERE 子句? [英] Slick: Is there a way to create a WHERE clause with a regex?

问题描述

我正在寻找一个光滑的等价物

I look for a slick equivalient to

select * from users where last_name ~* '[\w]*son';

例如，当数据库中有以下名称时:

So for example when having the following names in the database:

first_name | last_name
----------------------
Tore       | Isakson
John       | Smith
Solveig    | Larsson
Marc       | Finnigan

结果是

first_name | last_name
----------------------
Tore       | Isakson
Solveig    | Larsson

我目前的解决方案是使用 SQLActionBuilder 来插入它

My current solution is to interpolate this with an SQLActionBuilder like

val pattern = "[\\w]*son"
val action = sql""" SELECT * FROM users WHERE last_name ~* ${pattern}; """.as[User]

但这不是我想要的方式.我更喜欢像

But this is not the way I would like to have it. I would prefer something like

users.filter(_.last_name matchRegex "[\\w]*son")   // <- This does not exist

如果相关:我使用 Postgres.

In case it is relevant: I use Postgres.

推荐答案

(这个答案是在 Slick: How can I combine带有 SQL IN 语句的 SQL LIKE 语句)

尽管 Slick 不支持开箱即用的 ~* 运算符，但您可以自己添加它.这将为您提供一种使用提升嵌入样式的 Slick 查询来执行查询的方法.

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 use a 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:有没有办法用正则表达式创建 WHERE 子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！