Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
  • 04:44
    deusaquilus commented #2111
  • 04:40
    deusaquilus commented #1792
  • 04:09
    deusaquilus commented #2111
  • 04:05
    deusaquilus commented #1792
  • 04:03
    deusaquilus commented #1792
  • 02:36
    CLAassistant commented #1045
  • Aug 11 15:56
    CLAassistant commented #1045
  • Aug 11 07:07
    renovate[bot] edited #2351
  • Aug 11 00:03
    scala-steward opened #2567
  • Aug 10 23:37

    deusaquilus on virtual-prepared-statements

    Add section on batch-query opti… Virtual Prepared Statements (compare)

  • Aug 10 16:30

    deusaquilus on website

    Add section on batch-query opti… (compare)

  • Aug 10 16:06

    deusaquilus on master

    Add section on batch-query opti… (compare)

  • Aug 10 15:37

    deusaquilus on v4.3.0

    (compare)

  • Aug 10 15:37

    deusaquilus on website

    (compare)

  • Aug 10 15:37

    deusaquilus on master

    Setting version to 4.3.0 Setting version to 4.3.1-SNAPSH… (compare)

  • Aug 10 15:36

    deusaquilus on website

    (compare)

  • Aug 10 14:46
    deusaquilus closed #2566
  • Aug 10 14:46

    deusaquilus on master

    Trigger Release 4.3.0 (#2566) (compare)

  • Aug 10 14:12
    deusaquilus opened #2566
  • Aug 10 11:52

    deusaquilus on values-clause-batch-insert

    (compare)

Li Haoyi
@lihaoyi-databricks
don't worry too much if it's not possible, we'll find some other way around the problem
Alexander Ioffe
@deusaquilus
interesting
we're getting closer
Naftoli Gugenheim
@nafg
The problem is that t is a record
Alexander Ioffe
@deusaquilus
yup
Naftoli Gugenheim
@nafg
not a single value
What is a Query[String]
Is there such a thing as a resultset of scalars? Each row has to be a record, no?
Alexander Ioffe
@deusaquilus
there is actually
@lihaoyi-databricks I'm pretty sure I got it this time:
case class Unnest(unnest: String)

val strings = quote { 
  querySchema[Unnest]("now()")
    .map(t => infix"UNNEST(${lift(Seq("foo","bar"))})".pure.as[String]) 
}

run {
  query[ResultName]
    .rightJoin(infix"${strings}".pure.as[Query[Unnest]])
    .on(_.text == _.unnest)
    .map{case (rnOpt, n) => rnOpt.map(_.id)}
}
Alexander Ioffe
@deusaquilus
@lihaoyi-databricks ?
I checked the query that came out of this, it should really work...
This variation should also work and it's a bit less hacky:
case class Unnest(unnest: String)

val strings = quote { 
  querySchema[Unnest]("now()")
    .map(t => Unnest(infix"UNNEST(${lift(Seq("foo","bar"))})".as[String])).nested
}

run {
  query[ResultName]
    .rightJoin(strings)
    .on(_.text == _.unnest)
    .map{case (rnOpt, n) => rnOpt.map(_.id)}
}
Both of these queries produce this:
SELECT x1.id FROM ResultName x1 RIGHT JOIN (SELECT UNNEST(?) AS unnest FROM now() t) AS x2 ON x1.text = x2.unnest
the first variation produces that without the as unnest part
Li Haoyi
@lihaoyi-databricks
lemme take a look
hah seems to work
unfortunately I have no idea how this works :/ not sure if I dare start using it in production lol
Alexander Ioffe
@deusaquilus
This is an interesting oddity of postgres, when you unnest an array it returns a record with the 'unnest' column
Li Haoyi
@lihaoyi-databricks
is the issue that we cannot join on an array of strings, and instead have to convert it to a table before we can join on it?
Alexander Ioffe
@deusaquilus
what I did was to simulate that by creating an 'Unnest' row type
yup
Li Haoyi
@lihaoyi-databricks
what's the querySchema and now() thing all about
Alexander Ioffe
@deusaquilus
That's to force Quill to treat the unnest(array) as a table so it can be right-joined
now() is just a function in postgres that returns the time
Li Haoyi
@lihaoyi-databricks
can we use .as[Query[Unnest]] to do that
Alexander Ioffe
@deusaquilus
Hum...
Li Haoyi
@lihaoyi-databricks
like
val strings = quote(infix"UNNEST(${liftQuery(Seq("...", "...")}").as[Query[String])
Alexander Ioffe
@deusaquilus
Yeah
that produces the same query:
@ case class Unnest(unnest: String)

@ val strings = quote {
    infix"UNNEST(${lift(Seq("foo","bar"))})".as[Query[Unnest]].nested
  }
@ run {
    query[ResultName]
      .rightJoin(strings)
      .on(_.text == _.unnest)
      .map{case (rnOpt, n) => rnOpt.map(_.id)}
  }
cmd67.sc:1: SELECT x1.id FROM ResultName x1 RIGHT JOIN (SELECT x.unnest FROM (UNNEST(?)) AS x) AS x2 ON x1.text = x2.unnest
I over-complicated it
Yup, that's the best variation so far
oh
wait
I'm not sure if that will work
Li Haoyi
@lihaoyi-databricks
this gives as syntax error
Alexander Ioffe
@deusaquilus
Yeah
in that case you need to manually specify the "SELECT" part
This should work:
@ case class Unnest(unnest: String)

@ val strings = quote {
    infix"SELECT UNNEST(${lift(Seq("foo","bar"))})".as[Query[Unnest]].nested
  }

@ run {
  query[ResultName]
    .rightJoin(strings)
    .on(_.text == _.unnest)
    .map{case (rnOpt, n) => rnOpt.map(_.id)}
}
It yields:
 SELECT x1.id FROM ResultName x1 RIGHT JOIN (SELECT x.unnest FROM (SELECT UNNEST(?)) AS x) AS x2 ON x1.text = x2.unnest
Li Haoyi
@lihaoyi-databricks
yeah that works
what's that .nested thing do?
is it to force a subquery
Alexander Ioffe
@deusaquilus
yup
let me double-check if it's needed
Li Haoyi
@lihaoyi-databricks
seems to work without it
Alexander Ioffe
@deusaquilus
Yup
all good
Li Haoyi
@lihaoyi-databricks
so this is the state of the art
@ ctx.run {
      query[db.ResultName]
        .rightJoin(infix"SELECT UNNEST(${lift(Seq("foo","test-shard-local-database"))})".as[io.getquill.Query[Unnest]])
        .on(_.text == _.unnest)
        .map{case (rnOpt, n) => rnOpt.map(_.id)}
    }
cmd12.sc:1: SELECT x1.id FROM result_name x1 RIGHT JOIN (SELECT UNNEST(?)) AS x2 ON x1.text = x2.unnest
val res12 = ctx.run {
                    ^
res12: List[Option[Long]] = List(None, Some(2674443566L))
Alexander Ioffe
@deusaquilus
lol