how can I INSERT one-to-many object with quill?

Question

I have a simple Parent object:

case class ParentCreate(name: String, age: Int, kids: Seq[String])

Now, in the database I have 2 tables to represent this, perent & kid, cause a parent can have many kids, and in the kid table I have a forein key to parentId.

the creation of the tables looks like this:

CREATE TABLE Parent (
    parentId int NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    age int NOT NULL,
    PRIMARY KEY (parentId)
);

CREATE TABLE Kid (
    kidId int NOT NULL AUTO_INCREMENT,
    name: varchar(255) NOT NULL,
    parentId int NOT NULL,
    PRIMARY KEY (kidId),
    FOREIGN KEY (parentId) REFERENCES Parent(parentId)
);

So now when I get a request of ParentCreate object, I need to have another layer of case classes that will represent the database structure, so I need to transform ParentCreate object to Parent and Kid objects:

Parent(name: String, age: Int)

Kid(name: String)

cause this is how the data is modeled in the database.

my issue is the I get a ParentCreate request that still dont have parentId, and to insert the kids I need the kids parentId...

How this is done the best way with quill?

appreciate any help :)

jmkoni · Answer 1 · 2018-01-26T15:33:07.463

This is the way I did it in my project:

case class KidId(value: Long) extends AnyVal
case class ParentId(value: Long) extends AnyVal

case class ParentRecord(id: ParentId
                  , name: String
                  , age: Int
                 )

case class Parent(id: ParentId
                  , name: String
                  , age: Int
                 )

object Parent {
  def createNew(name: String, age: Int, id: ParentId = ParentId(0L)): Parent = {
    Parent(id
      , name
      , age
    )
  }
}


case class KidRecord(
                      id: KidId
                      , parentId: ParentId
                      , name: String
                    )

case class Kid(
                id: KidId
                , parent: Parent
                , name: String
               )

object Kid {
  def createNew(name: String, parent: Parent): Kid = {
    Kid(KidId(0L)
      , parent
      , name
    )
  }
}

and then here is the quill code that would go in a different package

def createParent(parent: Parent): ParentId = {
  ctx.run(quote {
      query[ParentRecord].insert (
        _.name -> lift (parent.name)
        , _.age -> lift (parent.age)
      ).returning (_.id)
    }
  )
}

def createKid(kid: Kid): KidId = {
  ctx.run(quote {
      query[KidRecord].insert(
        _.name -> lift(kid.name)
        , _.parentId -> lift(kid.parent.id)
      ).returning(_.id)
    }
  )
}

and then used by this controller code

def createParentController(name: String, age: Integer, kids: Seq[String]) = {
  val parentToCreate = Parent.createNew(name, age)
  val parentId = createParent(parentToCreate)
  val createdParent = Parent.createNew(name, age, parentId)
  for (kid <- kids) {
    createKidController(kid, parentToCreate)
  }
}

def createKidController(name: String, parent: Parent) = {
  val kidToCreate = Kid.createNew(name, parent)
  val kidId = createKid(kidToCreate)
}

The primary thing is that you need to create the parent first and then pass the parent to whatever method you are using to create the kid. Caveat: I'm still fairly new to Scala and Quill.

thanks so much for the answer! im wondering, shouldnt you use transaction or something to insure the insertion will go through only if the whole process is done properly? what kind of protection or error handling do you use for this process? thanks! @jmkoni — John Deer, Jan 27 '18 at 00:17
seems like if the kid creation failed the parent would of been in the db anyway.. — John Deer, Jan 27 '18 at 23:27
So in my case, I would still want the parent object created and I'm not passing in an array to the parent object. I'll make an edit to the solution showing a potential way to handle errors. — jmkoni, Jan 28 '18 at 16:25
Nevermind, did some research and I'm not seeing any examples of quill errors being returned/what they would look like. It looks like quill enables transactions (http://getquill.io/#quotation-io-monad-io-monad-and-transactions), but there's not a solid example on how to do this with multiple queries. Asking on gitter.im/getquill/quill. — jmkoni, Jan 28 '18 at 16:42
No one answered, but there is this similar question that has some suggestions regarding transactions: https://stackoverflow.com/questions/48488962/failing-to-use-transactions-in-quill-to-insert-one-to-many-relational-objects — jmkoni, Jan 29 '18 at 12:28

how can I INSERT one-to-many object with quill?

1 Answers1