+ Start a Discussion
Ashley@WCELAshley@WCEL 

List of Lists using a single SOQL statement

Hello all. First time poster here who has relied heavily on these forums to learn Apex. Thank you!

 

Here's a problem that I hope might generate some discussion.

 

My goal was to take a list of Opportunities and group them by Account, not using a group by clause, rather, creating a list of lists such that each sublist contains opportunities for a unique AccountID. Given a list of opportunities (Opp1...Opp5), the end result should look something like this:

 

AccountA:

   Opp1

   Opp2

AccountB:

   Opp2

   Opp3

   Opp4

AccountC:

   Opp5

 

My first attempted solution is as follows where opps is passed as the function parameter. I had hoped this would return a list of Accounts with the relevant Opportunities in the relationship, but it did not work. The Account list is created as expected but the Opportunities relationship is empty.

 

 

Set<Id> addressees = new Set<Id>();
for(Opportunity o:opps){
  addressees.add(o.Id);
}
List<Account> a = [Select Id, Name, (Select Id, Name, Amount, CloseDate, IsWon, CharitableReceipt__c From Account.Opportunities Where Opportunity.Id in:opps) From Account where Id in :addressees];
return a;

 

 

In the end, I solved the problem using a for loop but it is not as elegant as a single SOQL statement. I'm curious if my original solution is workable.

 

public List<List<Opportunity>> groupOpportunities(List<Opportunity> opps){
        
Set<Id> accts = new Set<Id>();
List<List<Opportunity>> groupedOpps = new List<List<Opportunity>>();
        
//create an account control sets
for(Opportunity o:opps){
   accts.add(o.AccountId);
}
        
//add a new opp list for each account in the control set
for(Id i:accts){
   List<Opportunity> tempOpps = new List<Opportunity>();
   for(Opportunity o:opps){
      if (o.AccountId == i){tempOpps.add(o);}
   }
   groupedOpps.add(tempOpps);
}
        
return groupedOpps;

}

 

Best Answer chosen by Admin (Salesforce Developers) 
Avidev9Avidev9

I just saw that there was a lil problem with your original code

Set<Id> addressees = new Set<Id>();
for(Opportunity o:opps){
  addressees.add(o.AccountId); //should be account id
}
List<Account> accList = [Select Id, Name, (Select Id, Name, Amount, CloseDate, IsWon, CharitableReceipt__c From Opportunities Where Id in:opps) From Account where Id in :addressees];
return a;

 

The above code should work without a problem. and opps should be grouped by accounts

from where you are calling this method ?

 

 

 

All Answers

crop1645crop1645

This will get you almost what you need in a workable solution:

 

Map<ID,Account> aIdToAcctWOpposMap = new Map<ID,Account> ([select id, name, (select id, name, closeDate from Opportunities) from Account]); 

 The only thing this won't do is sort the Accounts as map keys aren't sorted; you can use the SOQL to sort the Oppos within each Account, however

Ankur_Tyagi.ax1759Ankur_Tyagi.ax1759
Hi Ashley,

You can achieve this requirement by using Map<String, List<Opportunity>>. In this map key would be Account Id/Account Name and value will be a list Opportunities. To filter the Opportunities for Accounts please refer code snippet below..

Map<String, List<Opportunity>> accOppMap = new Map<String, List<Opportunity>>();

for(String accId : accountIdsList) {
List<Opportunity> tempOpps = new List<Opportunity>();
for(Opportunity opty : OpportunitiesList) {
if (accId == opty.AccountId) {
tempOpps.add(opty);
}
}
if(tempOpps.size() > 0)
accOppMap.put(accId, tempOpps);
}

Map accOppMap will contain Account Is as key and list of Opportunities as value.


Thanks,
Ankur
Avidev9Avidev9

I just saw that there was a lil problem with your original code

Set<Id> addressees = new Set<Id>();
for(Opportunity o:opps){
  addressees.add(o.AccountId); //should be account id
}
List<Account> accList = [Select Id, Name, (Select Id, Name, Amount, CloseDate, IsWon, CharitableReceipt__c From Opportunities Where Id in:opps) From Account where Id in :addressees];
return a;

 

The above code should work without a problem. and opps should be grouped by accounts

from where you are calling this method ?

 

 

 

This was selected as the best answer
Ashley@WCELAshley@WCEL

Holy mackerel - that's all it was. Sure helps to have a second set of eyes scan your code for those late night mistakes.

 

Works as expected now and this solution should scale better than the for loop. Returning a list of accounts and linked opportunities is also a more intuitive interface than returning a list of lists or a map of lists. In answer to your question, I am calling it from a test class right now but it will be used by other custom classes.

 

Thanks Avi !!

 

And thanks everyone else for the Map suggestions. I learned something new.