Trouble understanding how to create a collection in my trigger

Question

Hello and thank you for taking the time to reading my question,

Many of you have already helped me out a lot with great ideas and pointers. One of them is to create a collection and I am puzzled on how i would do this especially in an optimized manner.

I am creating an opportunity trigger with before update and after update, it will in the end create a Contract(Service Agreement), I need to gather information from the opportunity, its child quote(there is a synced checkbox that marks the quote as the correct quote) and verify is there is a child contract already created.

The code:

    List<Opportunity> opp =     [SELECT
                                      Id, StageName, (SELECT Id FROM Service_Contracts__r), 
                                      (SELECT Id From Quotes )
                                     FROM
                                      Opportunity 
                                     WHERE
                                      Id IN:oppIds
                                     ]; 

When I try to run this in the 'Execute Anonymous' the quote and service contract are not present.

Peter mentioned "Related objects (either parent or child) are not populated in Trigger.new/old. Best practice would be to do a "collect" step with the initial for loop to get only the records you want to work with, make the DB trip with SOQL"

So how would I populate with SOQL efficiently?

In my execute I used an opp id to pull up relevant data (WHERE ID = '0123456')

System.debug(oppsFromDb); // Pulls the opp information but nothing on quotes or Scontracts

System.debug(oppsFromDb[0].Quotes); //runs but no variables.

Thank you again for all your time!

Answer 1

Assuming the Quote existed before the Opportunity was being updated, you should be able reference the 'Quotes' relationship in the before/ after update trigger on Opportunity.

The relationship fields are not available in before/after insert triggers, in which case you have to aggregate ids and then query for them.

eg

trigger OpportunityBefore on Opportunity (before insert){

Map<Id, Opportunity> accIdsMap = new Map<Id, Opportunity>{};

for(Opportunity opp : trigger.new){
System.debug(opp.Account.Name); //this will not work as the relationship is not available
accIds.add(opp.AccountId, opp); //this id will be available, so lets collect account ids
}

//now we will query for collected account ids
for(Account acc : [Select Id, Name from Account where Id in :accIdsMap.keySet()])
System.debug(acc.Name + ' is the account for ' + accIdsMap.get(acc.Id) + ' Opportunity');

}

If I've understood you right, this blog post might help.

Answer 2

This is strange. I just ran:

List<Account> accounts = [SELECT 
                              Id, (SELECT Id FROM Opportunities) 
                          FROM 
                              Account 
                          WHERE 
                              Id = '001F000000jlUsTIAU' 
                          LIMIT 1
];

System.debug(accounts.get(0).Opportunities);

I got the following result:

15:24:24:094 USER_DEBUG [3]|DEBUG|(Opportunity:{RecordTypeId=012F0000000j9RCIAY, AccountId=001F000000jlUsTIAU, Id=006F000000IlAmXIAV})

This would tend to indicate that the call is working properly.

---

However, this is really interesting. I ran the following:

List<Account> accounts = [SELECT 
                              Id, (SELECT Id FROM Opportunities__r) 
                          FROM 
                              Account 
                          WHERE 
                              Id = '001F000000jlUsTIAU' 
                          LIMIT 1
];

System.debug(accounts.get(0).Opportunities__r);

and got the following result:

15:40:40:071 USER_DEBUG [3]|DEBUG|()

This would indicate that apparently you can type any relationship into SOQL and if the relationship isn't valid it just returns nothing.

EDIT: Ran this in a brand new org and it didn't compile. I guess someone made a custom relationship between Opportunity and Account in the org I was in. Not sure why, but this makes much more sense! Disregard the above section on Opportunities__r and the issue of this compiling!

---

Using the above logic, I tried this:

List<Account> accounts = [SELECT 
                              Id, (SELECT Id FROM Non_Existent_Object__r) 
                          FROM 
                              Account 
                          WHERE 
                              Id = '001F000000jlUsTIAU' 
                          LIMIT 1
];

System.debug(accounts.get(0).Non_Existent_Object__r);

This instantly returned:

line 1, column 26: Didn't understand relationship 'Non_Existent_Object_r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '_r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

This shows that what I originally thought was incorrect with just allowing any relationship name being a valid option (which makes much more sense). This is much more consistent with my understanding of Relationship queries.

---

I decided to do a bit more research and using the sObject Describe methods in conjunction with the ChildRelationship Methods. I ran the following code:

Schema.DescribeSObjectResult d = Account.sObjectType.getDescribe();
List<Schema.ChildRelationship> relationships = d.getChildRelationships();

String listOfRelationshipNames = '';
for(Schema.ChildRelationship rel:relationships){
    listOfRelationshipNames = listOfRelationshipNames + rel.getRelationshipName() + ', ';
}

System.debug(listOfRelationshipNames);

which returned the following:

16:02:01:131 USER_DEBUG [9]|DEBUG|null, AccountContactRoles, Feeds, Histories, AccountPartnersFrom, AccountPartnersTo, Shares, ActivityHistories, Assets, Attachments, Cases, Contacts, null, null, Contracts, FeedSubscriptionsForEntity, null, Events, null, null, null, Notes, NotesAndAttachments, OpenActivities, Opportunities, OpportunityPartnersTo, PartnersFrom, PartnersTo, ProcessInstances, ProcessSteps, null, Tasks, null, null

So, tweaking that code a bit to run it for Opportunity:

Schema.DescribeSObjectResult d = Opportunity.sObjectType.getDescribe();
List<Schema.ChildRelationship> relationships = d.getChildRelationships();

String listOfRelationshipNames = '';
for(Schema.ChildRelationship rel:relationships){
    listOfRelationshipNames = listOfRelationshipNames + rel.getRelationshipName() + ', ';
}

System.debug(listOfRelationshipNames);

which returned:

16:02:39:094 USER_DEBUG [9]|DEBUG|AccountPartners, ActivityHistories, Attachments, null, null, FeedSubscriptionsForEntity, Events, null, null, null, Notes, NotesAndAttachments, OpenActivities, OpportunityCompetitors, OpportunityContactRoles, Feeds, Histories, OpportunityHistories, OpportunityLineItems, OpportunityPartnersFrom, Shares, Partners, ProcessInstances, ProcessSteps, Tasks

All of this seems kind of out there. I don't even see the Quotes child relationship (although this may not be active in my demo org?).

---

Long story short, I am not sure how much this will help you, but this should work. It is most likely an issue with the Quotes relationship name being something different than what you are attempting to access. I would try messing around with that. Hopefully someone smarter than me can jump in and help look over what we have found and give you a better answer. Good luck!