+ Start a Discussion
paddingtonpaddington 

Querying Nested Maps

I'm trying to be really good and not loop SOQL, so I have tried rewriting some code to see how I get on. I've come up against a problem with querying nested Maps, which I didn't have when looping SOQL.

 

This is how I used to create a Map in a method that deals with a list of accounts accs passed from the trigger and a related object called assessments:

 

 

//Create nested map of account ID to map of SAQ assessment IDs to recommendations
Map<ID,Map<ID,String>> assessMap = new Map<ID,Map<ID,String>> ();
Map<ID,String> recMap = new Map<ID,String> ();
for(Account a:accs){
for(Assessment__c assess : [SELECT Id, Recommendation__c
FROM Assessment__c
WHERE Account__c = :a.Id
AND RecordTypeId = '01280000000BR0aAAG']){
recMap.put(assess.Id,assess.Recommendation__c);
}
assessMap.put(a.Id,recMap);
}

 

Note how SOQL is called for each account, which increases the chance of hitting governor limits.

 

I've tried rewriting to this and it seems to validate okay, but doesn't work in testing - I seem to be de-referencing a null object on the last line, which must be the fact that I'm getting from the null map I created before. Any ideas on how to fix this?

 

 

//Create nested map of account ID to map of SAQ assessment IDs to recommendations
Map<ID,Map<ID,String>> assessMap = new Map<ID,Map<ID,String>> ();

for(Account a:accs){
assessMap.put(a.Id,null);
}

for(Assessment__c assess : [SELECT Id, Account__c, Recommendation__c
FROM Assessment__c
WHERE RecordTypeId = '01280000000BR0aAAG'
AND Account__c
IN :Utility.setOfIdFromListOfSObject(accs)]){
assessMap.get(assess.Account__c).put(assess.Id,assess.Recommendation__c);
}

 

Be aware that I've used a utility class to get a set of the account IDs from the list that is passed to the method.

The second problem comes when I try to query the map. For each account in the list passed from the trigger I want to iterate through the assessment IDs that have been mapped to it, for which I'm trying to use the keySet() method. This returns the compile error: "Loop variable must be of type Id", which it must be, as that is the key type specified by the nested Map. What am I doing wrong?

 

 

//Iterate through accounts
for(Account a:accs){

//Declare counting variables
Integer approvecount=0;
Integer declinecount=0;
Integer refertwocount=0;

//Count approvals, declinations and referrals for each account, then decide appropriate action
for(Assessment__c assess :assessMap.get(a.Id).keySet()){
if(assessMap.get(a.Id).get(assess.Id) == 'Approve'){
approvecount++;
}
if(assessMap.get(a.Id).get(assess.Id) == 'Decline'){
declinecount++;
}
if(assessMap.get(a.Id).get(assess.Id) == 'Refer to Level 2'){
refertwocount++;
}
}

 

 

 

 

 

Message Edited by paddington on 11-10-2009 08:42 AM
Best Answer chosen by Admin (Salesforce Developers) 
paddingtonpaddington

Right, I've just solved it, so here's the code for the record. The first problem was with the creatioin of a nested map. This syntax does the job without looped SOQL:

 

//Create nested map of account ID to map of SAQ assessment IDs to recommendations
Map<ID,Map<ID,String>> assessMap = new Map<ID,Map<ID,String>> ();
Map<ID,String> assessNestMap = new Map<ID,String> ();

for(Assessment__c assess : [SELECT Id, Account__c, Recommendation__c, Decline_Refer_Feedback__c
FROM Assessment__c
WHERE RecordTypeId = '01280000000BR0aAAG'
AND Account__c
IN :Utility.setOfIdFromListOfSObject(accs)]){
assessNestMap.put(assess.Id,assess.Recommendation__c);
assessMap.put(assess.Account__c,recNestMap);
}

 The second problem was with querying the nested map. I was trying to do it using an sObject bind on the keySet when I needed to do an ID bind:

 

//Iterate through accounts
for(Account a:accs){

//Declare counting variables
Integer approvecount=0;
Integer declinecount=0;
Integer refertwocount=0;

//Count approvals, declinations and referrals for each account, then decide appropriate action
for(ID assess :assessMap.get(a.Id).keySet()){
if(assessMap.get(a.Id).get(assess) == 'Approve'){
approvecount++;
}
if(assessMap.get(a.Id).get(assess) == 'Decline'){
declinecount++;
}
if(assessMap.get(a.Id).get(assess) == 'Refer to Level 2'){
refertwocount++;
}
}

 Hope this helps people trying to nest maps.