function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
sindhu surusindhu suru 

Generate a Map of Id and Map

SOQL question : Here is what i am trying to do.

for (claim__c clm : shortlistedclaims){
Map<Date,Decimal> tad = new Map<Date,Decimal>();

AggregateResult[] groupedResults = [SELECT Begin_Date__c, SUM(Wages__c) FROM Wages__c where claim__c =: clm.id GROUP BY Begin_Date__c];

for (AggregateResult ar : groupedResults) {
tad.put((Date)ar.get('Begin_Date__c'),(Decimal)ar.get('expr0'));
}

ClaimtoMAP.put(clm.id,tad);
}

This works fine but leads to 'Too many SOQL statements' error since the query is inside the loop.

In general, Is it possible to populate Map<Id,Map<Date,Decimal>> in a single query? If not, any workaround to this problem?
ForceComForceCom
Hi, 

You might have to write two for loops and declare a set.

set<Id> clmIdSet = new set<Id>();

for(claim__c clm: shortListedClaims){
     clmIdSet.add(clm.Id);
}

AggregateResult[] groupedResults = [SELECT claim__c, Begin_Date__c, SUM(Wages__c) FROM Wages__c where claim__c IN: clmIdSet GROUP BY Begin_Date__c];

for (AggregateResult ar : groupedResults) {
Map<Date,Decimal> tad = new Map<Date,Decimal>();
tad.put((Date)ar.get('Begin_Date__c'),(Decimal)ar.get('expr1'));

ClaimtoMAP.put((Id)are.get('claim__c'),tad);

}

I hope this gets you an idea. 

Thank You
sindhu surusindhu suru
That was helpful . Thanks!
Here is something similar i came up with.

for(claim__c c:shortlistedclaims)
     shortlistedClaimIds.add(c.Id);

for (AggregateResult groupedResults: [SELECT Claim__c, Begin_Date__c, SUM(Wages__c) FROM Wages__c where Claim__c IN :shortlistedClaimIds GROUP BY Claim__c, Begin_Date__c]) {
      Id claimId = (Id) groupedResults.get('Claim__c');
      Map<Date, Decimal> tadByDate = benefitDatetoTAD.get(claimId);
      if (tadByDate == null) {
          benefitDatetoTAD.put(claimId, tadByDate = new Map<Date, Decimal>());
      }
      tadByDate.put((Date) groupedResults.get(Begin_Date__c'), (Decimal) groupedResults.get('expr0'));
  }

But i am having trouble doing the same to populate Map<Id,List<Offsets__c>>. How do i do this outside the loop.

for (claim__c clm : shortlistedclaims){
Offsets__c[] Offsets = [SELECT Offset_Start_Date__c,Offset_End_Date__c,Daily_Rate__c FROM Offsets__c where Void_Flag__c = false AND claim__c =: clm.id ];
   
      claimtoOffset.put(clm.id,offsets);
}
ForceComForceCom
Hi ,

I assume claimToOffset map is your Map<Id, List<Offsets__c>>.
On that assumption , you are putting values into the map inside the for loop which is correct .

Thanks.
sindhu surusindhu suru
But i am running select statement inside loop which is not recommended.
ForceComForceCom
Yes , the select statement is not at all recommended inside the loop, it will blow up your code.

At the top of code , you have this piece.
for(claim__c c:shortlistedclaims){
     shortlistedClaimIds.add(c.Id);
}

Add your select statement outside the loop .
Offsets__c[] Offsets = [SELECT Offset_Start_Date__c,Offset_End_Date__c,Daily_Rate__c FROM Offsets__c where Void_Flag__c = false AND claim__c IN: shortListstedClaimIds ];

If you have observed in the select statement I changed the where condition. I hope this fixes your issue.

Thank You
sindhu surusindhu suru
Yes, now i have all the offsets belonging to shortlistedclaims. But how do i group them claim by claim and populate the map?
ForceComForceCom
Hi,

Please find below the code.

Map<Id , offsets__c> offsetToClaimMap = new Map<Id, offsets__c>();
Map<Id, Map<Id,offsets__c>> claimToOffsetMap= new Map<Id, Map<Id, offsets__c>>();

for(claim__c c:shortlistedclaims){
     shortlistedClaimIds.add(c.Id);
}


List<Offsets__c> Offsets =[SELECT Offset_Start_Date__c,Offset_End_Date__c,Daily_Rate__c FROM Offsets__c where Void_Flag__c = false AND claim__c IN: shortListedClaimIds];
for(Offsets__c offset : Offsets){
     offsetToClaimMap.put(offset.claim__c, offset);
}

for (AggregateResult groupedResults: [SELECT Claim__c, Begin_Date__c, SUM(Wages__c) FROM Wages__c where Claim__c IN :shortlistedClaimIds GROUP BY Claim__c, Begin_Date__c]) {
      Id claimId = (Id) groupedResults.get('Claim__c');
      Map<Date, Decimal> tadByDate = benefitDatetoTAD.get(claimId);
      if (tadByDate == null) {
          benefitDatetoTAD.put(claimId, tadByDate = new Map<Date, Decimal>());
      }
      tadByDate.put((Date) groupedResults.get(Begin_Date__c'), (Decimal) groupedResults.get('expr0'));
  }

for (claim__c clm : shortlistedclaims){
      claimtoOffset.put(clm.id,offsetToClaimMap);
}

Then you can get each offset per claim based on the key which is claim Id.

I hope this gives you an idea.

Thank You
sindhu surusindhu suru
for (claim__c clm : shortlistedclaims){
      claimtoOffset.put(clm.id,offsetToClaimMap);
}

With this code, each claim will have exact copy of the offsetToClaimMap, which is incorrect.

Here is what i came up with . This is a 2 pass strategy.

for(Offsets__c off : [SELECT claim__c, Offset_Start_Date__c, Offset_End_Date__c, Daily_Rate__c FROM Offsets__c where Void_Flag__c = false AND claim__c IN :shortlistedClaimIds]){
    Id claimId = off.Claim__c;
    List<Offsets__c> offsetByClaim = claimtoOffset.get(claimId);
    if (offsetByClaim == null) {
          claimtoOffset.put(claimId, offsetByClaim = new List<Offsets__c>());
      }
       offsetByClaim.add(off);
  }

This is going to generate Map<Id,List<Offsets__c>>.