Looping through Inner Query Best Practice

Question

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

Answer 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.