1

Looking for some general guidance on the best way to deal with getting to and looping through inner queries of SOQL.

At a high level, I have a trigger on Opportunity stage. If the opportunity stage is closed won, I want to create new records based on the activities on the Account. This will include activities not only directly associated with the opportunity, but all activities from the associated account of the Opportunity.

I have queries that will look at the Activity History of the account and return the associated completed tasks. The problem I am running into, is that I then will need to reassociate these tasks back to the opportunity and not just the account when I create the new records.

To get to the inner query, also includes double for looping and while populating other fields on the new record, I will also have to loop through additional things. So going in that direction, my code is not efficient by any means.

I am relatively new to Apex and coding in general, but I think what I need to do is create a map of Opportunity Ids with a list of Account activities, but I am not sure how I would go about creating that map or if it is possible to map two different objects in a single map.

I am including the main query that I am currently using and a sample of the for loops I am referecning if I were to do it in this looping way.

List<Account> ah = [SELECT Id, Name (SELECT Id, Subject, CreatedDate, OwnerId, ActivityType FROM ActivityHistories WHERE CreatedDate = LAST_N_DAYS: 90) FROM Account WHERE Id IN (SELECT AccountId FROM Opportunity WHERE Id = :oppIds)];        

for(Account acc : ah)       
  for(ActivityHistory a : acc.ActivityHistories){
     for(Channel__c channel : channels){
           if(a.ActivityType == channel.Name){
                  newinf.Channel__c = channel.id; //newinf.CHannel__c is one of the fields on the new record I am creating
1

channels should be a Map for performance reasons:

Map<String, Id> channelsByName = new Map<String, Id>();
for(Channel__c channelRecord: channels) {
  channelsByName.put(channelRecord.Name, channelRecord.Id);
}

Which changes:

 for(Channel__c channel : channels){
       if(a.ActivityType == channel.Name){
              newinf.Channel__c = channel.id; 

To just:

newinf.Channel__c = channelsByName.get(a.ActivityType);

You can make the accounts a Map as well:

Map<Id, Account> accountMap = new Map<Id, Account>(ah);

Which you can then use to look up the accounts efficiently for each opportunity (presuming I understand your question correctly). In general, whenever you need to look up a thing efficiently, a Map is the way to go.

1
  • Yes, that makes sense on the map for channels and also the accounts. The one additional item is that the new record I am creating is master-detailed to opportunity. So I'm looping through accounts to get the activities, but I need to end up relating back to the opportunity which I am not sure how I can do that since I am just looping through accounts. – Reed Sep 8 '20 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.