how to translate update set SQL to scala quill?

Question

I have a schema in PostgreSql in which I want do update set for users_id field:

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

  PRIMARY KEY (oid)
);

The execute sql as this:

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

it update users_id array filed and do distinct operation.

In Quill, I have attempt with the method:

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

A exception occurred:

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

How can I execute the sql with scala Quill library? Different way always welcome!
Thanks

Update - more information

dependency is :

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

My driver instance is:

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

Besides, some simple sql has test successful.

Lorance, as not everybody here speaks Chineese, is it true that the error message says something like "The column index is out of range: 1, number of columns: 0" ? — SergGr, Dec 04 '17 at 06:30
@SergGr Yes. Sorry for that. I will modify it as English version. — LoranceChen, Dec 04 '17 at 07:03

jilen · Accepted Answer · 2017-12-04T06:36:42.323

4

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

edited Dec 04 '17 at 06:36

answered Dec 04 '17 at 05:26

jilen

5,633
3
35
84

Thanks.The sql is success. But a runtime error occurred: `Exception in thread "main" org.postgresql.util.PSQLException: 栏位索引超过许可范围：2，栏位数：1。`. Do you have any idea for the exception? – LoranceChen Dec 04 '17 at 05:56
@LoranceChen Can you execute the sql directly in the `postgresql` command line ? – jilen Dec 04 '17 at 06:02
Yes. Sql in postgresql is success. – LoranceChen Dec 04 '17 at 06:07
Could I test the sql in Quill with bare sql?Maybe the driver not support the statement. – LoranceChen Dec 04 '17 at 06:09
1

@LoranceChen Encoder the `uids` as array works, you can test sql is in postgres use `prepare` and `execute`. That is equivalent as quill generated sql statement – jilen Dec 04 '17 at 06:38
Hi, `prepare` and `execute` works well in postgres. It maybe caused by another problem.Give me some time to check please. – LoranceChen Dec 04 '17 at 07:39
code works now.It seems I'm use the old version of your code.Notice `uids` at just. – LoranceChen Dec 04 '17 at 07:49
Is there a way to construct Plain SQL query in the form of string dynamically and run it and model it to scala object ?? – user9920500 Feb 13 '20 at 05:37

how to translate update set SQL to scala quill?

Update - more information

1 Answers1