getquill/quill

Compile-time Language Integrated Query for Scala - Deprecated Please move to zio-quill in https://discord.gg/MYErPQgkHH

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

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

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

Where communities thrive

People

Repo info

Activity