Anorm Scala insert list of objects with nested list

Question

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.

Answer 1

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()
    }
  }
}

Answer 2

As indicated by its name, Anorm is not an ORM, and won't generate the statement for you.

You will have to determine the statements appropriate the represent the data and relationships (e.g. my Acolyte tutorial).

As for transaction, Anorm is a thin/smart wrapper around JDBC, so JDBC transaction semantic is keep. BTW Play provides .withTransaction on its DB resolution utility.