Aggregate SOQL with more than one 'group by'

Question

I am using an aggregate SOQL query with more than one 'group by'

I'm writing a class that fires on insert/update of a trigger on the OpportunityLineItem. This looks at a picklist field (Play__c) on Product2.

When Opportunity products are added (which may be in batches) I need to count the number of each possible variation in the picklist for each OpportunityProduct.

Opp 1 has 5 products added. 2 are Red, 2 are Black and one is White. There are a number of fields on the Opp: isRed, isBlack and IsWhite into which those number must go.

My first take is that an aggregate function would do. My Primary key is Oppid and Play_c in order to count Play_c:

AggregateResult[] OppLineItemsPlays = [select OpportunityId, COUNT(id), PricebookEntry.Product2.Play__c
           from OpportunityLineItem
           where OpportunityId in: oppid
           group by OpportunityId, PricebookEntry.Product2.Play__c];
  System.debug('\n ------------------->OppLineItemsPlays = ' + OppLineItemsPlays);

for(AggregateResult OLIPlays: OppLineItemsPlays) {
   opportunityCount.put((ID) OLIPlays.get('OpportunityId'), (Integer)OLIPlays.get('expr0'));
   System.debug('\n ------------------->Opportunity id = ' + OLIPlays.get('OpportunityId'));
   System.debug('\n ------------------->Play__c = ' + OLIPlays.get('Play__c'));
   System.debug('\n ------------------->count = ' + OLIPlays.get('expr0'));
   System.debug('\n ------------------->OLIPlays = ' + OLIPlays);
//  }

This gives me a count of all of the times that each Play has been selected in Products in OLIs and gives me the name of the play.

Here is my issue and its a bit silly: how would you proceed from here? My thoughts are to create a new map for every variation of Play and then add the Oppid, Count(Play__c). This means that I have to know every possible play (which I do) and can control the creation of new ones (which I can) Alternatively a map of ids to a map of strings to integers: [map> opptyPlayCount = new map>();]

But - the items in the aggregate are objects not strings or IDs...

if( !opptyPlayCount.containsKey( OLIPlays.get('OpportunityId') ) ) 
{
    opptyPlayCount.put( OLIPlays.get('OpportunityId'), new Map< string, integer >( ));
}
if( !opptyPlayCount.get( OLIPlays.get('OpportunityId') ).containsKey( OLIPlays.get('Play__c') )) 
{
    opptyPlayCount.get( OLIPlays.get('OpportunityId') ).put( OLIPlays.get('Play__c'), integer( ));
}
opptyPlayCount.get( OLIPlays.get('OpportunityId') ).get( OLIPlays.get('Play__c') ).add( OLIPlays.get('expr0'));

So the above doesn't work and I can do this:

integer CountofType = integer.ValueOF(OLIPLays.get('expr0'));
string NameofType = string.valueOf(OLIPlays.get('Play__c'));

but not for the ID

So when I get to saving this I have the Oppids.

for(Id opptyId : summedOpptyIds) {
         opps.add(new Opportunity(
          Id = opptyId,
          Booking_Amount__c = totalBookingAmts.get(opptyId)
          isRed = ................. but how do I get this?
         ));
        }

Answer 1

Build the map like this:

id oppId = Id.valueOf(String.valueOf(OLIPlays.get('OpportunityId')));
integer CountofType = integer.ValueOF(OLIPLays.get('expr0'));
string NameofType = string.valueOf(OLIPlays.get('Play__c'));

if( !opptyPlayCount.containsKey( oppId  ) ) 
{
    opptyPlayCount.put( oppId , new Map< string, integer >( ));
}
if( !opptyPlayCount.get( oppId  ).containsKey( NameofType )) 
{
    opptyPlayCount.get( oppId  ).put( NameofType, integer( ));
}
opptyPlayCount.get( oppId  ).get( NameofType ).add( CountofType );

Then retrieve the value like this:

if(opptyPlayCount.containsKey(opptyId)){
   if(opptyPlayCount.get(opptyId).containsKey('Red')){    
      isRed = opptyPlayCount.get(opptyId).get('Red');
   }
}