Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
  • 08:07

    deusaquilus on update-multi-value

    continue effort (compare)

  • 07:50

    deusaquilus on update-multi-value

    odd situations continue thoughts try to differentiate lifts with… (compare)

  • 07:18
    kampka synchronize #2569
  • 01:56
  • Aug 17 20:06
    kampka synchronize #2569
  • Aug 17 19:05
    kampka synchronize #2569
  • Aug 17 18:16
    CLAassistant commented #2569
  • Aug 17 18:13
    CLAassistant commented #2569
  • Aug 17 18:13
    kampka opened #2569
  • Aug 17 17:57
  • Aug 17 14:37

    deusaquilus on update-multi-value

    Another test rename to match class fix an issue (compare)

  • Aug 17 10:03
    Delphin1 starred zio/zio-quill
  • Aug 17 06:33
    nohallcaesars starred zio/zio-quill
  • Aug 17 05:13

    deusaquilus on update-multi-value

    continue (compare)

  • Aug 17 02:52

    deusaquilus on update-multi-value

    fix stuff (compare)

  • Aug 17 01:07

    deusaquilus on update-multi-value

    jdbc tests working continue head banging and 2 more (compare)

  • Aug 16 06:44

    deusaquilus on update-multi-value

    continue rename a major class continue and 3 more (compare)

  • Aug 15 13:05
    tOverney commented #2060
  • Aug 14 08:34

    deusaquilus on update-multi-value

    continue various progress continuing to work on example (compare)

Li Haoyi
@lihaoyi-databricks
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
Li Haoyi
@lihaoyi-databricks
basically the only thing I was missing is the UNNEST thing to turn the array into a table
Alexander Ioffe
@deusaquilus
yeah
I don't think I could adapt liftQuery to this kind of functionality
The problem is that postgres expects a query coming out of unnest, not a scalar
Li Haoyi
@lihaoyi-databricks
a bit of a weird postgres quirk but I guess not the worst one I've hit
Alexander Ioffe
@deusaquilus
yeah, lots of that in postgres
Li Haoyi
@lihaoyi-databricks
doesn't compare to the time where deleting old records made the query planner go haywire and start doing table scans
Alexander Ioffe
@deusaquilus
heh, maybe liftUnnestQuery(list)
SQL query planners are the bane of my existance
half the time if they'd just cache a complex sub-view the entire problem would be solved but there's no directive in SQL to do that. You'd think that what CTFs do but it's not
Li Haoyi
@lihaoyi-databricks
basically SQL is the wrong level of abstraction. It tries to hide the implementation, but whether a query runs in 40milliseconds or 40minutes actually matters for a lot of use cases...
Most of the time I would be happier writing query plans directly
Alexander Ioffe
@deusaquilus
Lol, welcome to my life
Li Haoyi
@lihaoyi-databricks
Like I want to specify what index the query will use, and if I want a table scan I'll ask for it thank you very much
Alexander Ioffe
@deusaquilus
The problem is, if we start doing that we're basically back to writing stored-procs... that's essentially what they do
Li Haoyi
@lihaoyi-databricks
sounds good to me