Quill way of doing INSERT INTO ... SELECT FROM

Question

I'm trying to translate simple INSERT INTO...SELECT FROM query into a quote in Quill. First of all, I am failing to find a built-in way to do this, so ended up trying out to use infix query

val rawQuery = quote { (secondTableValues: List[Int]) => {
        infix"""
                INSERT INTO my_table (second_table_id)
                VALUES (
                  ${secondTableValues.map(stid => (SELECT id FROM second_table where id = $stid)).mkString(",")}}
                )
          """.as[Insert[Any]]
    }}

databaseClient.run(rawQuery(List(1,2,3)))

This however does not compile as Quill can't build Ast for the query.

What I ended up doing is have a raw query and not using quotes and run it with executeAction.

So two questions

How would you do INSERT INTO...SELECT FROM in a built-in way?
What is wrong with the infix version above?

score 2 · Answer 1 · answered Sep 26 '19 at 13:45

  import io.getquill._

  val ctx = new SqlMirrorContext(MirrorSqlDialect, Literal)

  import ctx._

  case class Table1(id: Int)

  case class Table2(id: Int, name: String)


  def contains(list: List[Int]) = {
    //SELECT e.id,'hello' FROM Table1 e WHERE e.id IN (?)
    val q = quote(
      query[Table1].filter(e => liftQuery(list).contains(e.id)).map(e => Table2(e.id, "hello"))
    )
    //insert into table2 SELECT e.id, 'hello' FROM Table1 e WHERE e.id IN (?)
    // `${..}` is expect ast , not string
    quote(infix"insert into table2 ${q}".as[Insert[Any]])
  }


  // test
  val list = List(1)
  ctx.run(contains(list))

Quill way of doing INSERT INTO ... SELECT FROM

1 Answers1