Apex Code Development You need to sign in to do that
Don't have an account?
Ashley@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;
}
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
This will get you almost what you need in a workable solution:
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
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
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 ?
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.