postgresql - 如何将更新集 SQL 翻译成 scala quill？

Question

我在 PostgreSql 中有一个模式，我想在其中为 users_id 字段执行 update set:

CREATE TABLE if not exists rooms (
  oid char(24),
  owner_id char(24) not null,
  users_id text[],

  PRIMARY KEY (oid)
);

执行sql如下:

update rooms set users_id = (select array_agg(distinct e) from  
unnest(users_id || '{5a16f7ce77c8a2b22406fb86}') e) where  oid =  
'5a16f7ce77c8a2b22406fb86';

它更新 users_id 数组字段并执行 distinct 操作。

在 Quill 中，我尝试使用以下方法:

def addUserInRoom(userId: ObjectId, roomId: ObjectId): Unit = {
    val q = quote(
      (uid: String, rid: String) =>
        infix"""update rooms set users_id = (select array_agg(distinct e) from unnest(users_id || '{${uid}}') e) where  oid = '${rid}'""".as[Query[Long]]
    )

    run(q(lift(userId.toString), lift(roomId.toString)))
}

发生异常:

Exception in thread "main" org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
    at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:65)
    at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:128)
    at org.postgresql.jdbc.PgPreparedStatement.bindString(PgPreparedStatement.java:1029)
    at org.postgresql.jdbc.PgPreparedStatement.setString(PgPreparedStatement.java:369)
    at org.postgresql.jdbc.PgPreparedStatement.setString(PgPreparedStatement.java:353)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setString(HikariProxyPreparedStatement.java)
    at io.getquill.context.jdbc.Encoders.$anonfun$stringEncoder$2(Encoders.scala:44)
    at io.getquill.context.jdbc.Encoders.$anonfun$stringEncoder$2$adapted(Encoders.scala:44)
...

如何使用 scala Quill 库执行 sql？不同的方式总是受欢迎的!
谢谢

更新 - 更多信息

依赖是:

  "org.postgresql" % "postgresql" % "42.1.4",
  "io.getquill" %% "quill-jdbc" % "2.2.0",

我的驱动实例是:

lazy val ctx = new PostgresJdbcContext(
    NamingStrategy(SnakeCase, PostgresEscape),
    AppConfig.quill
  )

此外，一些简单的sql已经测试成功。

Answer 1

  lazy val ctx = new PostgresJdbcContext(SnakeCase, "ctx")
  import ctx._

  case class Rooms(oid: String, ownerId: String, usersId: Seq[String])

  def foo(oid: String, uid: String) = {
    val uids: Seq[String] = Seq(uid)
    val v = quote(infix"(select array_agg(distinct e) from unnest(users_id || ${lift(uids)}) e)".as[Seq[String]])

    val q = quote {
      query[Rooms].filter(_.oid == lift(oid))
        .update(_.usersId -> unquote(v))
    }
    ctx.run(q)
  }
  def main(args: Array[String]): Unit = {
    println(foo("1", "2"))
  }