DML Insert List of Child Records and Junction Records in a single DML Statement

Question

I've 3 Custom Objects (List) :

• List <C1> (Child)
  • fields : Id, Name
• List <C2> (Child)
  • fields : Id, Name
• List <C3> (Junction)
  • fields : IdC1, IdC2, Name

I was able to get C1 IDS as the record was created before my DML transaction to C3.

So to be able to insert data in C3, i must insert records in C2 before and get the returned Ids.

The simple way would be to :

• Loop through C2 object
• Insert data with DML statement
• Get the returned Id and build my C3 Junction objects List.

But as i know that calling a DML statement in a for loop should be totally avoid, i don't know how i could achieve this in a different way.

Could you please advice me ?

Thank you,

Answer 1

Using the Unit of Work pattern backed up by the fflib-apex-common GITHUB library, you can delegate most of this work and make your coding somewhat simpler

UnitOfWork will insert objects in a user-defined order so C2 will always be inserted before C3 (junction)

Code would look something like this:

... build map of C1 suitable for location when building C3
fflib_ISobjectUnitOfWork uow = Application.UnitOfWork.newInstance();
for (C2__c c2: c2s) {  // assuming list of C2__c has already been constructed in memory
   uow.registerNew(c2);  // tell UoW there's a new C2
   C3__c c3 = new C3(...);  // build C3__c fields except lookups
   uow.registerNew(c3,C3__c.C2__c,c2); // tell Uow that this c3 has c2 as parent
   uow.registerRelationship(c3,C3__c.C1__c,someMapLookupofTheC1YouNeed); // tell Uow this C3 has C1 as parent
}
uow.commitWork(); // all C2s and C3s get inserted in proper order

This pattern is extremely flexible and extensible, allowing one to construct elaborate chains of sobjecttypes without having to manage too many intermediate maps yourself. I have used it, for example, to construct Account-Contact-Order-OrderItem-PricebookEntry-Product2 where Order has lookups to Pricebook2, Account and Contact, OrderItem is child of Order, and OrderItem has lookup to PricebookEntry.

Even better, because you are using the UnitOfWork layer, you can use ApexMocks to do unit testing, verifying that you are constructing the correct objects without having to do actual DML - thus making your testmethods easier to write as well as run faster. ApexMocks is not a substitute for integration testing but a tool for better unit tests.

An in-depth discussion of all of this can be found (in addition to trailhead) in an excellent book by Andy Fawcett.

Answer 2

You are right, first you need to insert child records and then the parent one.

If C3 is junction object, the assumption is that lists of C1 and C2 should contain the same amount of records.

Use that to your advantage and build C3 list with help of traditional for loop, that allows you to use an index number, and create instances of C3 junction records.

At the end you can insert all C3 records in one DML statement and out of the loop.

List<C1__c> c1List = new List<C1__c>{
    new C1__c(Name = 'C1 1'), new C1__c(Name = 'C1 2')
};
insert c1List;

List<C2__c> c2List = new List<C2__c>{
    new C2__c(Name = 'C2 1'), new C2__c(Name = 'C2 2')
};
insert c2List;

List<C3__c> c3JunctionList = new List<C3__c>();
for (Integer i = 0; i < c1List.size(); i++) {
    C1__c c1 = c1List.get(i);
    C2__c c2 = c2List.get(i);
    String c3Name = 'C3 ' + String.valueOf(i + 1);

    c3JunctionList.add(new C3__c(
        Name = c3Name,
        C1_Lookup__c = c1.Id,
        C2_Lookup__c = c2.Id
    ));
}
insert c3JunctionList;