Anorm Scala insert list of objects with nested list

I find myself in need of inserting a sequence of elements with a sequence of nested elements into a PostgreSQL database, preferably with a single statement, because I am returning a Future. I am using Scala Play with Anorm.

My data looks something like below.

case class Question(id: Long, titel: String)
case class Answer(questionId: Long, text: String)

In db it looks like this:

CREATE TABLE questions (
  question_id SERIAL PRIMARY KEY NOT NULL,
  titel TEXT NOT NULL,
);

CREATE TABLE answers (
  answer_id SERIAL PRIMARY KEY NOT NULL,
  question_id INT NOT NULL,
  text TEXT NOT NULL,
  FOREIGN KEY (question_id) REFERENCES questions(question_id) ON DELETE CASCADE
);

My function would look something like this:

def saveFormQuestions(questions: Seq[Question], answers: Seq[Answer]): Future[Long] = {
  Future {
    db.withConnection{ implicit c =>
      SQL(
        // sql
      ).executeInsert()
    }
  }
}

Somehow, in Anorm, SQL or both, I have to do the following, preferably in a single transaction:

• foreach question in questions
  • insert question into questions
  • foreach answer in answers, where answer.questionId == old question.id
    • insert answer into answers with new question id gained from question insert

I am new with Scala Play, so I might have made some assumptions I shouldn't have. Any ideas to get me started would be appreciated.

I solved it with logic inside the db.withConnection block. Somehow I assumed that you had to have a single SQL statement inside db.withConnection, which turned out not to be true. So like this:

val idMap = scala.collection.mutable.Map[Long,Long]() // structure to hold map of old ids to new
db.withConnection { implicit conn =>
  // save all questions and gather map of the new ids to the old
  for (q <- questions) {
    val id: Long = SQL("INSERT INTO questions (titel) VALUES ({titel})")
      .on('titel -> q.titel)
      .executeInsert(scalar[Long].single)
    idMap(q.id) = id
  }

  // save answers with new question ids
  if (answers.nonEmpty) {
    for (a <- answers ) {
      SQL("INSERT INTO answers (question_id, text) VALUES ({qid}, {text});")
        .on('qid -> idMap(a.questionId), 'text -> a.text).execute()
    }
  }
}